From: "Michael Stassen" > 2) In general, you should'nt put things in the JOIN condition which are not > part of the JOIN criteria. For example, the condition "c.salescode = > '$salescode_param'" is a restriction on which rows in c to consider, not a > condition of the JOIN to t or tt, so it belongs in the WHERE clause.
This is what the manual says. Experience shows that moving criteria from the WHERE clause to the JOIN condition may in fact restrict the rows that are used for the JOIN. Smaller record sets in a JOIN often mean faster queries. When optimizing queries I usually check whether the criterium limit the number of records used for the JOIN and if yes, I try and see if it makes the query faster if I move that criterium to the JOIN condition. IMHO it's counterproductive to join large sets of records and afterwards remove records with the WHERE clause when it was already clear that those records shouldn't have been included in the first place. Maybe the optimizer should've taken care of this, but at the moment the optimizer will not make this kind of optimizations. > 2) Since the goal is to choose transactions for a particular customer during > a specific date range, your query might benefit from a multi-column index in > table transaction on (custcode, date). If you do add that multi-column Multi-column indexes can be wonderful! It does however require you to know what kind of queries you will be running. If this is not a problem for a certain application one can start to analyze which columns will be used in a query (in JOIN, ORDER BY, WHERE, GROUP BY, etc. excepting the SELECT part itself). With an overview of the columns that are used in the query you can start to 'design' the indexes that will -- if possible -- cover all queries with the least number of indexes. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
