vendredi 31 juillet 2015

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.

Aucun commentaire:

Enregistrer un commentaire