Generally in MySQL we send queries massaged to a point where optimizer doesn’t have to think about anything. In our major user database environment 99.9% of queries don’t have alternative query plans available (either because of forced indexes or just straightforward Primary Key read). We have various other systems and from time to time we have to do SQL work there and chase optimizer errors.
There’re multiple places where optimizer can make a choice in very basic queries, for example:
- Which index returns less rows
- Which index can be used for ORDER BY
A query that I was looking asked a very basic question, on a job
instances table, show state and status for latest-by-ID entry for
job name=’Ship Christmas Presents’ (real name was a bit different
;-). So, it was SELECT c,d FROM t WHERE b=X ORDER BY a DESC
LIMIT 1
, where PK is (a)
and a possible index
is on …