CODEWITHSUNDEEP
mysql
Josh
Josh

Reputation: 945

MySQL using a index_merge and intersect instead of a ref and where

I have a query that is producing different optimizer results on development and production (same database, same data).

On my machine the query runs in ~5ms

On production the query runs in ~300-500ms

The only difference I can find is this row in the EXPLAIN EXTENDED result (and the mysql versions):

Good query

*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: activities
         type: ref
possible_keys: index_activities_on_is_archived,index_activities_on_equipment_id,index_activities_on_date_completed,index_activities_on_shop_id
          key: index_activities_on_shop_id
      key_len: 5
          ref: const
         rows: 1127
     filtered: 100.00
        Extra: Using where

Bad query

*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: activities
         type: index_merge
possible_keys: index_activities_on_is_archived,index_activities_on_equipment_id,index_activities_on_date_completed,index_activities_on_shop_id
          key: index_activities_on_shop_id,index_activities_on_is_archived
      key_len: 5,2
          ref: NULL
         rows: 1060
        Extra: Using intersect(index_activities_on_shop_id,index_activities_on_is_archived); Using where

I'm at a loss as to where to start debugging this. Is this a issue with the mysql versions and production running an old database?

My local version: 5.5.15 Production: 5.0.95-log

Thanks in advance

Upvotes: 0

Views: 1407

Answers (2)

Josh
Josh

Reputation: 945

It ended up being the query cache being disabled in production, turning that on fixed the issue.

Upvotes: 0

user1191247
user1191247

Reputation: 12973

You could try running ANALYZE TABLE to update the statistics but I suspect it is down to improvements in the optimiser. You could also try using an index hint in your query to tell MySQL not to use the index_activities_on_is_archived index. The low cardinality of this index is likely to be harmful to performance anyway. I would delete it.

Upvotes: 1

Related Questions