MySQL Forums
Forum List  »  Optimizer & Parser

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

Options: ReplyQuote


Subject
Views
Written By
Posted
4332
January 02, 2007 09:24AM
2158
January 02, 2007 08:11PM
2158
January 03, 2007 05:35PM
2164
January 03, 2007 06:10PM
2154
January 04, 2007 05:49AM
Re: Two general MySQL questions
2153
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.