StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMySQL gurus: Why 2 queries give different 'explain' index use results?
text
Body
copied!<p>This query:</p> <pre><code>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' </code></pre> <p>correctly uses the <code>Donation.order_line_id</code> and <code>Lineitem.id</code> indexes, shown in this EXPLAIN output:</p> <pre> 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</pre> <p>However, this query, which simply includes another field:</p> <pre><code>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' </code></pre> <p>Shows that the <code>Donation</code> table does not use an index:</p> <pre> 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</pre> <p>All of the <code>_id</code> 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.</p> <p>As requested by James C, here are the table definitions:</p> <pre><code>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 </code></pre> <p>I also did some reading about cardinality, and it looks like both the <code>Donations.npo_id</code> and <code>Donations.order_line_id</code> have a cardinality of 2. Hopefully this suggests something useful?</p> <p>I'm thinking that a <code>USE INDEX</code> 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?!?</p> <p>Thanks for your brainpower!</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload