{ Josh Rendek }

<3 Ruby & Go

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
 6 possible_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
 6 possible_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.

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.

comments powered by Disqus