Responses embedded below "mel list_php" <[EMAIL PROTECTED]> wrote on 03/18/2005 05:57:29 AM:
> Hi Shawn, > > Thank you very much, I'm impressed by the time you took to answer me, and > the quality of the reply!!! > I forwarded the answer to my friend. > I'm wondering, I knew the mechanism of temporary tables, but as I've never > used it I was trying the left join way. > Here is a summary of my questions: > - why using inner join here?is there any difference with using a left join?I > thought using a left join would decrease the number of results. The primary difference between a LEFT JOIN and an INNER JOIN is that with an INNER JOIN matching records MUST exist in both tables before they are considered for evaluation by the WHERE clause. You usually retrieve MORE records with a LEFT JOIN than an INNER JOIN but that depends on your data, too. In no case can an LEFT JOIN return fewer records than an INNER JOIN, all other conditions being equal. > - do you know why without group by my query was running very fast and become > so slow with the group by?when it does a group by it's scanning the whole > table or an other reason? The GROUP BY clause requests that the engine make another processing pass through the records that satisfy your WHERE clause conditions in order to aggregate records according to the columns you specified. It's that second pass and the processing that occurs within it that makes a "grouped" query slower to finish than an "ungrouped" one. (NOTE: Some ungrouped query results are so large that a grouped result may actually be _useful_ sooner due to less data transfer between the server and your application) > - I don't know if his version of mysql supports subqueries, but I was > wondering if it is possible to replace the temporary tables by subqueries > and keeping the same efficiency (my friend told me he would like to have > only one sql query). I have found very few cases where subqueries outperformed temp (or special-purpose, permanent) tables especially when working with larger amounts of data. Of course, subquery performance varies according to the nature of the subquery (can it be evaluated just once or does it have to have to be evaluated for each and every row of the result), the complexity of the subquery, and the hardware your server is on. The only way to know for sure is to develop a subquery version of this query and test it with your/their hardware. > Once again thank you very much for your help, I will give temporary tables > an other chance!!! > Melanie > You are most welcome! Shawn Green Database Administrator Unimin Corporation - Spruce Pine > <BIG snip>
