MySQL choosing wrong indexes

10-13-2011, 01:37 PM

Hello, I'm hoping someone can point me in the right direction on this. I've had trouble searching because I don't quite know what this sort of problem is officially called. We're running MySQL 5.5.9 with four slaves and one master. I've been running into a problem where one or more of the slaves will choose the wrong indexes on fairly complicated queries.

For instance, with the same query, the first row on the explain will be:

I've tried analyzing the tables. I've tried rebuilding the indexes. A force index will solve it, but I'd like to get to the underlying issue of why some servers, with identical configurations, are returning different index paths. What is this sort of problem called? Anyone know of any reference material that can help here?

Comment

I agree with gmouse. Note that what's changing is not just the index, but the join order. I would suggest using STRAIGHT_JOIN hints instead of FORCE INDEX. Sometimes scanning a big table and doing a "star schema" join is actually much more efficient than what seems to be a bunch of fast index lookups, due to use of sequential I/O instead of random, but in your case I take it this is not desired.

Comment

Thank you, I'd never seen STRAIGHT_JOIN, and some initial tests looks like it's working. (One of my biggest problems is that I'm one of those sysadmins that was dragooned into being a DBA without any real training)

The ordering does seem to be the major issue. It's not a small issue of I/O, it's the difference between seconds and hours. The whole reason this came up is when I started seeing queries running very slow, and one that normally took about five seconds was running for half an hour when I killed it. I've just tried that one with STRAIGHT_JOIN instead of JOIN and it seems to have corrected it.

I wish there was some way I could correct the underlying way MySQL is deciding between the patths, though, rather than going after individual queries.

Comment

I wish there was some way I could correct the underlying way MySQL is deciding between the patths, though, rather than going after individual queries.

Haven't we all wished for a magic bullet.

Problem is that the algorithm to choose execution path is a huge compromise between speed (both to sample data and to figure out an execution path), assumptions about your hardware and a lot of different types/combinations of queries.
Sometimes the wrong path is chosen, but if you would start to tweak the general parameters you would probably end up with a worse compromise.

My general suggestion when a database server chooses a really bad execution path is that either the query or the existing indexes are not spot on, which means that the optimizer has a hard time to decide on the best path and are more prone to choose a bad one. Which may or may not be true in your case.