MySQL choosing wrong indexes

MySQL & MariaDB Other MySQL® Questions
1

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:

*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: wrsemployees
partitions: NULL
type: ref
possible_keys: PRIMARY,wrsemployees_practice_id
key: wrsemployees_practice_id
key_len: 5
ref: const
rows: 24
Extra: Using where; Using index; Using temporary; Using filesort

While on another server, it will be:
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: wrsemployeeordersnotesections
partitions: NULL
type: ALL
possible_keys: PRIMARY,wrsemployeeordersnotesections_employee_order_id,wrse mployeeordersnotesections_section_id
key: NULL
key_len: NULL
ref: NULL
rows: 3941822
Extra: Using where; Using temporary; Using filesort

Obviously, the performance on the latter is simply unacceptable.

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?

2

It has to do with the cardinality of the indices. They are always computed (after rebuilding, when analyzing, or when starting MySQL) based on a subset of your rows, not all rows.

Maybe it works to add some rows to your index wrsemployees_practice_id, but that will depend on your query.

3

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.