mySQL TempraryTables from XOJO

I have some SQL that I have been fighting with for a while. There are millions of records involved and simple indexing of a complex Query didn’t give me the results I need. So I was forced to use a Temporary Table in Mysql. Then I send a second query on the temporary table so all the recursive SQL statements only act on a few dozen records rather than a few million. (Its ugly but it gets me there)

Here is what the two statements look like.

CREATE temporary TABLE ghostTable AS 
  (SELECT id, widgetNo  
   FROM   customers 
   WHERE  type = 'gold' 
          AND Field1 NOT LIKE '%foo%' 
          AND Field2 LIKE '%bar%' 
          AND cost > 10000 
          AND profit < 999999 
          AND theOtherThing > 1000 
          AND state IN( 'TX', 'WV', 'NV', 'IL' ) 
          AND county <> 'cook' 
          AND whatever IS NOT NULL 
          AND  color <> '');

Then I call

SELECT id, widgetNo from ghostTable group by widgetNo having count(*) = 1;

It all works nice and neat from MySQL Workbench. But how do I do it from Xojo?

Lets assume:

dim FirstSQL as string = //All the stuff from the top statement (CREATE temporary TABLE ghostTable AS ........blah blah blah) dim SecondSQL as string = //The query on the ghost table (SELECT id, widgetNo ...blah blah)

Question #1
Can I call sqlExecute (FirstSQL)
Followed BY sqlSelect(SecondSQL)

Question #2
I assume I cant use the same table all the time “ghostTable” could be called by 30 different users. So I will have to devise something that does:

CREATE temporary TABLE Name_W_SessionStamp  AS .....

When will it die? And how do I kill it off if necessary form Xojo?

Question #3
What else am I missing?

doesn’t make sense…

SELECT id, widgetNo  
   FROM   customers 
   WHERE  type = 'gold' 
          AND Field1 NOT LIKE '%foo%' 
          AND Field2 LIKE '%bar%' 
          AND cost > 10000 
          AND profit < 999999 
          AND theOtherThing > 1000 
          AND state IN( 'TX', 'WV', 'NV', 'IL' ) 
          AND county <> 'cook' 
          AND whatever IS NOT NULL 
          AND  color <> ''
          group by widgetNo
          having count(*) = 1;

should work just as fast… since to make your ghost table still involves all the processing from the WHERE statement. Have you done an EXPLAIN on the query to see where it is having a fit?

Also… if you have 30 users needing the same answer… cache it in another table… and update THAT table whenever the answer might change… I am ASSUMING that it is the same WHERE conditions, and not 30 different ones based on user requests.

I dont understand it either: But the single query takes 634 / 601 / 619 seconds. (Yes I ran it 3 times) The ghostTable takes less than 4 seconds.

I have not done an EXPLAIN. (Never knew EXPLAIN existed, actually!) Ill give it a try. Nice to learn new things. Thanks.

Despite the help: My curiosity still is unsatisfied :

How would you create and query a Temporary table from Xojo?

How long does it live?

How do you kill it off?

[quote=20861:@Jay Menna]How would you create and query a Temporary table from Xojo?
[/quote]
Exactly the same way you did in the Workbench. Use SQLExecute and pass it the same sql you used above.

[quote]How long does it live?
[/quote]
Until you close the connection.

Close the connection.