I had a query that, after adding indexes, was taking anywhere from 1.5 to 5ms to return on my local machine. In production and staging environments it was taking 500+ms to return.
The query was producing different optimizer paths:
The good optimizer:
1*************************** 2. row ***************************
2 id: 1
3 select_type: SIMPLE
4 table: activities
5 type: ref
6possible_keys: index_activities_on_is_archived,index_activities_on_equipment_id,index_activities_on_date_completed,index_activities_on_shop_id
7 key: index_activities_on_shop_id
8 key_len: 5
9 ref: const
10 rows: 1127
11 filtered: 100.00
12 Extra: Using where
The bad optimizer:
1*************************** 2. row ***************************
2 id: 1
3 select_type: SIMPLE
4 table: activities
5 type: index_merge
6possible_keys: index_activities_on_is_archived,index_activities_on_equipment_id,index_activities_on_date_completed,index_activities_on_shop_id
7 key: index_activities_on_shop_id,index_activities_on_is_archived
8 key_len: 5,2
9 ref: NULL
10 rows: 1060
11 Extra: Using intersect(index_activities_on_shop_id,index_activities_on_is_archived); Using where
My first thought was it might have been the MySQL versions since I was running 5.5 locally and 5.0 in production, but that turned out not to be the case.