CODEWITHSUNDEEP
mysqlsqloptimization
iopener
iopener

Reputation: 541

MySQL gurus: Why 2 queries give different 'explain' index use results?

This query:

explain 
SELECT `Lineitem`.`id`, `Donation`.`id`, `Donation`.`order_line_id` 
  FROM `order_line` AS `Lineitem` 
       LEFT JOIN `donations` AS `Donation` 
       ON (`Donation`.`order_line_id` = `Lineitem`.`id`) 
 WHERE `Lineitem`.`session_id` = '1'

correctly uses the Donation.order_line_id and Lineitem.id indexes, shown in this EXPLAIN output:

id  select_type     table       type    possible_keys   key             key_len     ref         rows    Extra
1   SIMPLE          Lineitem    ref     session_id      session_id      97          const       1       Using where; Using index
1   SIMPLE          Donation    ref     order_line_id   order_line_id   4           Lineitem.id 2       Using index

However, this query, which simply includes another field:

explain 
SELECT `Lineitem`.`id`, `Donation`.`id`, `Donation`.`npo_id`, 
       `Donation`.`order_line_id` 
  FROM `order_line` AS `Lineitem`  
       LEFT JOIN `donations` AS `Donation` 
       ON (`Donation`.`order_line_id` = `Lineitem`.`id`) 
 WHERE `Lineitem`.`session_id` = '1'

Shows that the Donation table does not use an index:

id  select_type     table       type    possible_keys   key             key_len     ref     rows    Extra
1   SIMPLE          Lineitem    ref     session_id      session_id      97          const   1       Using where; Using index
1   SIMPLE          Donation    ALL     order_line_id   NULL            NULL        NULL    3

All of the _id fields in the tables are indexed, but I can't figure out how adding this field into the list of selected fields causes the index to be dropped.

As requested by James C, here are the table definitions:

CREATE TABLE `donations` (
`id` int(10) unsigned NOT NULL auto_increment,
`npo_id` int(10) unsigned NOT NULL,
`order_line_detail_id` int(10) unsigned NOT NULL default '0',
`order_line_id` int(10) unsigned NOT NULL default '0',
`created` datetime default NULL,
`modified` datetime default NULL,
PRIMARY KEY  (`id`),
KEY `npo_id` (`npo_id`),
KEY `order_line_id` (`order_line_id`),
KEY `order_line_detail_id` (`order_line_detail_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8

CREATE TABLE `order_line` (
`id` bigint(20) unsigned NOT NULL auto_increment,
`order_id` bigint(20) NOT NULL,
`npo_id` bigint(20) NOT NULL default '0',
`session_id` varchar(32) collate utf8_unicode_ci default NULL,
`created` datetime default NULL,
PRIMARY KEY  (`id`),
KEY `order_id` (`order_id`),
KEY `npo_id` (`npo_id`),
KEY `session_id` (`session_id`)
) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8

I also did some reading about cardinality, and it looks like both the Donations.npo_id and Donations.order_line_id have a cardinality of 2. Hopefully this suggests something useful?

I'm thinking that a USE INDEX might solve the problem, but I'm using an ORM that makes this a bit tricky, and I don't understand why it wouldn't grab the correct index when the JOIN specifically names indexed fields?!?

Thanks for your brainpower!

Upvotes: 0

Views: 249

Answers (1)

James C
James C

Reputation: 14149

The first explain has "uses index" at the end. This means that it was able to find the rows and return the result for the query by just looking at the index and not having to fetch/analyse any row data.

In the second query you add a row that's likely not indexed. This means that MySQL has to look at the data of the table. I'm not sure why the optimiser chose to do a table scan but I think it's likely that if the table is fairly small it's easier for it to just read everything than trying to pick out details for individual rows.

edit: I think adding the following indexes will improve things even more and let all of the join use indexes only:

ALTER TABLE order_line ADD INDEX(session_id, id);
ALTER TABLE donations ADD INDEX(order_line_id, npo_id, id)

This will allow order_line to to find the rows using session_id and then return id and also allow donations to join onto order_line_id and then return the other two columns.

Looking at the auto_increment values can I assume that there's not much data in there. It's worth noting that the amount of data in the tables will have an effect on the query plan and it's good practice to put some sample data in there to test things out. For more detail have a look in this blog post I made some time back: http://webmonkeyuk.wordpress.com/2010/09/27/what-makes-a-good-mysql-index-part-2-cardinality/

Upvotes: 3

Related Questions