Re: why does explain plan change?

[Fabio Sgamo]

Just a quick answer to "So the optimizer is choosing these different plans seemingly at random, though I don't really think that would be the case. Why does something like this happen, and more importantly, how can I ensure the optimizer uses the more efficient plan in this case?"

The optimizer computes which it considers the best plan, every time a query is executed, and the plan is calculated on static and dinamic data (not randomly!). The numbers of records in every table involved, and the key distributions of the indexes used, are the dinamic data. As you noticed, running ANALYZE changed the plan (http://dev.mysql.com/doc/refman/5.0/en/analyze-table.html): after your tables got "scrambled" by insert/delete, the optimizer "wasn't in touch" with the actual data in tables, and ANALYZE did the right trick

Than, just learn how to use LEFT JOIN, making your select from one table only: the WHERE gets freed by joining condition, and it COULD be optimized even better.