{"id":810,"date":"2009-05-07T19:35:15","date_gmt":"2009-05-07T17:35:15","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=810"},"modified":"2009-08-10T20:19:51","modified_gmt":"2009-08-10T18:19:51","slug":"mysql-not-being-able-to-utilize-a-compound-index","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/mysql-not-being-able-to-utilize-a-compound-index","title":{"rendered":"MySQL not being able to utilize a compound index?"},"content":{"rendered":"

I came today upon a very strange issue. It seems like MySQL is unable to utilize a compound index when evaluating a plan for a query with a range condition. I’m looking for an explanation. I’ll appreciate any insight on this.<\/p>\n

Take a look at the following table:<\/p>\n

\n
CREATE TABLE `t` (\r\n  `a` int(11) NOT NULL default '0',\r\n  `b` int(11) NOT NULL default '0',\r\n  `c` int(11) default NULL,\r\n  PRIMARY KEY  (`a`,`b`)\r\n) ENGINE=InnoDB DEFAULT CHARSET=latin1<\/pre>\n<\/blockquote>\n
Filled with this data:<\/p>\n
\n
mysql> SELECT * FROM t;\r\n+---+---+------+\r\n| a | b | c    |\r\n+---+---+------+\r\n| 1 | 1 | NULL |\r\n| 1 | 2 | NULL |\r\n| 1 | 3 | NULL |\r\n| 1 | 4 | NULL |\r\n| 1 | 5 | NULL |\r\n| 2 | 1 | NULL |\r\n| 2 | 2 | NULL |\r\n| 2 | 3 | NULL |\r\n| 2 | 4 | NULL |\r\n| 2 | 5 | NULL |\r\n+---+---+------+\r\n10 rows in set (0.00 sec)<\/pre>\n<\/blockquote>\n
Now, it is known that I can query by tuples:<\/p>\n
\n
mysql> SELECT * FROM t WHERE (a,b) < (2,2);\r\n+---+---+------+\r\n| a | b | c    |\r\n+---+---+------+\r\n| 1 | 1 | NULL |\r\n| 1 | 2 | NULL |\r\n| 1 | 3 | NULL |\r\n| 1 | 4 | NULL |\r\n| 1 | 5 | NULL |\r\n| 2 | 1 | NULL |\r\n+---+---+------+\r\n6 rows in set (0.00 sec)<\/pre>\n<\/blockquote>\n
MySQL understands tuple comparison (e.g. (a,b) < (2.2)<\/strong>) and returns correct results. Now here’s my issue: I would assume the PRIMARY KEY is used – since it’s on (a,b)<\/strong> – so that’s a simple (well, compound) range condition. Alas:<\/p>\n
\n
mysql> EXPLAIN SELECT * FROM t WHERE (a,b) < (2,2)G\r\n*************************** 1. row ***************************\r\n           id: 1\r\n  select_type: SIMPLE\r\n        table: t\r\n         type: ALL\r\npossible_keys: NULL\r\n          key: NULL\r\n      key_len: NULL\r\n          ref: NULL\r\n         rows: 10\r\n        Extra: Using where\r\n1 row in set (0.00 sec)<\/pre>\n<\/blockquote>\n
We get a full table scan! Now, MySQL has no problem when I do an equality<\/em> search (e.g. (a,b) = (2,2)<\/strong>). In that case, the PRIMARY KEY is<\/em> utilized.<\/p>\n
Why, then, would it not use it in the range query? Why would it not consider it as a possible key (I could live with FORCE KEY if that would solve the problem). This problem undermines some efforts of mine for nice optimization tricks.<\/p>\n
[UPDATE<\/strong>: the above is a simplified version of a very large table I was using (~50M rows, ~30GB), and on which same results were achieved]<\/p>\n
Any ideas or suggestions?<\/p>\n","protected":false},"excerpt":{"rendered":"
I came today upon a very strange issue. It seems like MySQL is unable to utilize a compound index when evaluating a plan for a query with a range condition. I’m looking for an explanation. I’ll appreciate any insight on this.<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"enabled":false},"version":2}},"categories":[5],"tags":[26],"class_list":["post-810","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-indexing"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-d4","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/810","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/comments?post=810"}],"version-history":[{"count":8,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/810\/revisions"}],"predecessor-version":[{"id":1164,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/810\/revisions\/1164"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=810"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=810"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=810"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}