& Kubernetes 
MySQL Performance tuning constant references vs index merges and intersects
Apr 4, 2012 - 2 minutesI 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 whereThe 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 whereMy 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.
Next was to make sure my database was an exact replica of the one in production. After ensuring this I still ended up with the same results from the optimizer.
My last guess was server configuration. The issue ended up being query-cacheing being turned off in production and staging but not on my local machine. Turning this on, restarted mysqld, and re-running the query produced the good optmizer results on both my local machine and production.
GitHub