Re: Two general MySQL questions
Posted by:
Toa Sty
Date: January 04, 2007 10:53AM
For a), MySQL will execute the query with whichever table it thinks will be the most optimal. Which one it picks will depend on a bunch of factors, but it's not something you have to worry about in 99.9% of cases. (And yeah, if one table has a where clause applied which will make it significantly smaller than the other table then MySQL will most likely use it as the first table)
For b) The order of a multi-column index matters, but the creation order of separate indexes does not.
If you have a table that has a where clause one one col and a join condition on another, then adding a multi-col index on (where col, join col) may help you a little more then just an index on where col, but that's just if you can avoid looking at this table's data file entirely (which will depend on which cols your query selects) It's probably not worth it in most cases.
Toasty
Oh, and as for your original query about SQL_CALC_FOUND_ROWS, yeh MySQL will keep executing the query until all possibly rows have been looked for even though it returns just enough to match your limit, so if you're relying on limit optimisations to make your queries fast you should be very careful about using SQL_CALC_FOUND_ROWS indeed.
-----------------------------------------
email: 'toasty'*3 at gmail
Subject
Views
Written By
Posted
4408
January 02, 2007 09:24AM
2213
January 02, 2007 08:11PM
2186
January 03, 2007 05:35PM
2197
January 03, 2007 06:10PM
2184
January 04, 2007 05:49AM
Re: Two general MySQL questions
2220
January 04, 2007 10:53AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.