Trying to understand MySQL Internal Optimizer
This thread on the MySQL GD got me interested in how the EXPLAIN generates the number of rows examined for a query. The first place I hit was the TFM, but that was not helpful. I googled around for a bit and found (a pretty obvious now that I think of it) reference to EXPLAIN using the Optimizer. After a bit googling I ended up reading a fairly in depth page on the optimizer. After reading for about an hour I ran across this:
“SELECT * FROM Table1
WHERE column1 > ‘x’ AND column2 > ‘x’
ORDER BY column2;
if both column1 and column2 are indexed, the optimizer will choose an index on … column1. The fact that ordering takes place by column2 values does not affect the choice of driver in this case.”
This does not make any sense at all to me. If the rows are retrieved first through the column2 index and then the colum1 portion of the WHERE clause could be applied to further remove rows. A this point I would think a filesort would not be necessary because the column2 index retrieval would have everything order, and the column1 based reduction in rows would not break the order. I ended up testing it a bit and found is not accurate. Index merges can be used on queries like the above at a minimum . MySQL seems to not take into account a penalty from doing a filesort (Great, index hinting to rescue…) when deciding what index to use, so I find the entire document suspect.
Getting off the tangent I found little pertinent information in the docs, though there was a lot of good information which I am guessing is out of date, combined with a bad update or weird defacement. Many of the where clauses have portions like (0 = AND s1 = 5) which I initially thought was some sort of abbreviated syntax, but as I could not figure out how to get MySQL (strict) to accept it, I am going consider it erroneous.
At this point I have spent a couple of hours looking for the information without success, and I no longer care at this point. I will probably either start reading code or ask the MySQL GD, neither seems like a good option.