List:General Discussion« Previous MessageNext Message »
From:Daniel Kasak Date:February 5 2006 9:18pm
Subject:Re: MySQL says, "Ich don't think so" (Subquery woes)
View as plain text  
René Fournier wrote:
> Thanks! I will try this Monday. On a related note, does the 'temporary 
> table' approach (creating, locking, dropping) significantly slow down 
> queries? I haven't had to use them yet, and my application is somewhat 
> performance sensitive, so... just curious.
It's actually a *lot* faster if you put some effort into it. When you 
use a sub-query, any indexes on the original fields inside the sub-query 
are not used in the join further up ( ie the derived table has no 
indexes on it ). So if you have a lot of data coming through in the 
sub-query, it's very much worth it to create a temporary table *with* 
*indexes* on fields you will be joining on. I was hoping MySQL would 
support automatically indexing result fields inside a sub-query like 
this, but apparently it's not on the cards :(

Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: dkasak@stripped
MySQL says, "Ich don't think so" (Subquery woes)RenĂ© Fournier4 Feb
  • QL 4.1 or greater.Re: MySQL says, "Ich don't think so" (Subquery woes)Rhino5 Feb
  • Re: MySQL says, "Ich don't think so" (Subquery woes)Michael Stassen5 Feb
    • Re: MySQL says, "Ich don't think so" (Subquery woes)RenĂ© Fournier5 Feb
      • Re: MySQL says, "Ich don't think so" (Subquery woes)Daniel Kasak5 Feb