I have recently seen several cases when performance for MySQL queries on a single table was terrible. The reason was simple: the wrong indexes were added and so the execution plan was poor. Here are guidelines to help you optimize various kinds of single-table queries.
Disclaimer: I will be presenting general guidelines and I do
not intend to cover all scenarios. I am pretty confident that you
can find examples where what I am writing does not work, but I am
also confident that it will help you most of the time. Also I
will not discuss features you can find in MySQL 5.6+ like Index
Condition Pushdown to keep things simple. Be aware that such
features can actually make a significant difference in query
response time (for good or for bad).
What an index can do for you
An index can perform up to 3 actions: filter, sort/group and cover. While the first 2 actions are self-explanatory, not everyone may know what …[Read more]