[Solved] Query runs faster without an index. Why?

EverSQL Database Performance Knowledge Base

Query runs faster without an index. Why?

I have two tables. One of those tables has this schema:

CREATE TABLE `object_master_70974_` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `id_object` int(10) unsigned NOT NULL DEFAULT '0',
 `id_master` int(10) unsigned NOT NULL DEFAULT '0',
 `id_slave` int(10) unsigned NOT NULL DEFAULT '0',
 `id_field` bigint(20) unsigned NOT NULL DEFAULT '0',
 `id_slave_field` bigint(20) unsigned NOT NULL DEFAULT '0',
 PRIMARY KEY (`id`),
 UNIQUE KEY `id_object`    (`id_object`,`id_master`,`id_slave`,`id_field`,`id_slave_field`),
 KEY `id_object_2` (`id_object`,`id_master`,`id_field`,`id_slave_field`),
 KEY `id_object_3` (`id_object`,`id_slave`,`id_field`),
 KEY `id_object_4` (`id_object`,`id_slave_field`),
 KEY `id_object_5` (`id_object`,`id_master`,`id_slave`,`id_field`),
 KEY `id_object_6` (`id_object`,`id_master`,`id_slave`,`id_slave_field`),
 KEY `id_master` (`id_master`,`id_slave_field`),
 KEY `id_object_7` (`id_object`,`id_field`)
) ENGINE=InnoDB AUTO_INCREMENT=17827 DEFAULT CHARSET=utf8;

As you can see, there is an overlapping index KEY id_object_5 (id_object,id_master,id_slave,id_field) and there is no index that would cover these three fields: id_object, id_master, id_field. However, when I run these two queries:

SELECT f1.id 
FROM object_70974_ f1  
LEFT JOIN object_master_70974_ mss0 ON mss0.id_object IN (70974,71759)  
AND mss0.id_master = 71100 AND mss0.id_slave = 70912 AND mss0.id_field = f1.id

and

SELECT f1.id 
FROM object_70974_ f1  
LEFT JOIN object_master_70974_ mss0 ON mss0.id_object IN (70974,71759)  
AND mss0.id_master = 71100 AND mss0.id_field = f1.id

they both return the same number of rows (since in fact id_slave field does not really matter) - 3530, however, the first query is slower than the second query by one second - 8 and 7 seconds respectively. So, I guess I have to ask two questions - 1) why does the second query run faster, even though it does not use index and 2) why does the first query run so slowly and why does not it use an index (obviously). In short, what the heck is going on?

EDIT

This is the result of EXPLAIN command (identical for both queries):

"id"    "select_type"   "table" "type"  "possible_keys" "key"   "key_len"   "ref"   "rows"  "Extra"
"1" "SIMPLE"    "f1"    "index" \N  "attr_80420_"   "5" \N  "3340"  "Using index"
"1" "SIMPLE"    "mss0"  "ref"   "id_object,id_object_2,id_object_3,id_object_4,id_object_5,id_object_6,id_master,id_object_7"   "id_master" "4" "const" "3529"  "Using where"

EDIT

It's extremely interesting, because if I DROP id_master index (which for some strange reason is used by both queries), then it starts to use id_object_5 index.

EDIT

And, yes, with id_master index being dropped, both queries start to run super-fast. So, I guess there is some trouble with optimizer.

EDIT

I even have a guess what trouble faces the optimizer - it may be incorrectly treats id_slave_field field name in the key, as if it were two fields instead - id_slave and id_field. In this case it becomes reasonable, why it firstly used this key in both queries.

EDIT

Schema of object_70974_

CREATE TABLE `object_70974_` (
   `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
   `id_inherit` BIGINT(20) NOT NULL DEFAULT '0',
   `id_obj` INT(10) UNSIGNED NOT NULL DEFAULT '0',
   `if_control` TINYINT(1) NOT NULL DEFAULT '0',
   `id_order` BIGINT(20) NOT NULL DEFAULT '0',
   `if_archive` TINYINT(1) NOT NULL DEFAULT '0',
   `id_group` BIGINT(20) NOT NULL DEFAULT '0',
   `if_hist` SMALLINT(6) NOT NULL DEFAULT '0',
   `if_garbage` TINYINT(1) NOT NULL DEFAULT '0',
   `id_color` CHAR(6) DEFAULT NULL,
   `id_text` TINYINT(4) NOT NULL DEFAULT '0',
   `if_default` TINYINT(1) NOT NULL DEFAULT '0',
   `id_parent` BIGINT(20) NOT NULL DEFAULT '0',
   .... a long list of other fields
   PRIMARY KEY (`id`),
   KEY `id_order` (`id_order`)
) ENGINE=INNODB AUTO_INCREMENT=3636 DEFAULT CHARSET=utf8;

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Remove Redundant Left Joins (modified query below): Redundant LEFT JOINs (e.g. `object_master_70974_`) were detected in the query. Removing them will result in a performance improvement. In some cases, JOINs become redundant after an optimization is applied, such as when converting OR conditions to a UNION clause.
The optimized query:
SELECT
        f1.id 
    FROM
        object_70974_ f1

Related Articles



* original question posted on StackOverflow here.