{"id":194,"date":"2010-04-27T11:40:15","date_gmt":"2010-04-27T11:40:15","guid":{"rendered":"http:\/\/psyphi.net\/blog\/?p=194"},"modified":"2010-04-28T09:44:12","modified_gmt":"2010-04-28T09:44:12","slug":"a-little-sql-explain-example","status":"publish","type":"post","link":"https:\/\/psyphi.net\/blog\/2010\/04\/a-little-sql-explain-example\/","title":{"rendered":"A Little SQL “EXPLAIN” example"},"content":{"rendered":"
\"\"
http:\/\/www.flickr.com\/photos\/banyan_tree\/2802823634\/sizes\/m\/<\/figcaption><\/figure>\n

Background: I have a big table called “channel” with a few hundred thousand rows in – nothing vast, but big enough to cause some queries to run slower than I want.<\/p>\n

Today I was fixing something else and happened to run<\/p>\n

show full processlist;<\/code><\/pre>\n
I noticed this taking too long:<\/p>\n
SELECT payload FROM channel  LIMIT 398800,100;<\/code><\/pre>\n
This is a query used by some web-app paging code. Stupid really – payload<\/em> isn’t indexed and there’s no use of any other keys in that query. Ok – how to improve it? First of all, see what EXPLAIN says:<\/p>\n
mysql> explain SELECT payload FROM channel  LIMIT 398800,100;\r\n+----+-------------+---------+------+---------------+------+---------+------+--------+-------+\r\n| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows   | Extra |\r\n+----+-------------+---------+------+---------------+------+---------+------+--------+-------+\r\n|  1 | SIMPLE      | channel | ALL  | NULL          | NULL | NULL    | NULL | 721303 |       |\r\n+----+-------------+---------+------+---------------+------+---------+------+--------+-------+\r\n1 row in set (0.00 sec)<\/code><\/pre>\n
Ok. So the simplest way would be to limit a selection of id_channel (the primary key) then select payloads in that set. First I tried this:<\/p>\n
SELECT payload\r\nFROM channel\r\nWHERE id_channel IN (\r\n    SELECT id_channel FROM channel LIMIT 398800,100\r\n);<\/code><\/pre>\n
Seems straightforward, right? No, not really.<\/p>\n
ERROR 1235 (42000): This version of MySQL doesn't yet support\r\n  'LIMIT & IN\/ALL\/ANY\/SOME subquery'<\/code><\/pre>\n
Next!<\/p>\n
Second attempt, using a temporary table, selecting and saving the id_channels I’m interested in then using those in the actual query:<\/p>\n
CREATE TEMPORARY TABLE channel_tmp(\r\n  id_channel BIGINT UNSIGNED NOT NULL PRIMARY KEY\r\n) ENGINE=innodb;\r\n\r\nINSERT INTO channel_tmp(id_channel)\r\n  SELECT id_channel\r\n  FROM channel LIMIT 398800,100;\r\n\r\nSELECT payload\r\n  FROM channel\r\n  WHERE id_channel IN (\r\n    SELECT id_channel FROM channel_tmp\r\n  );<\/code><\/pre>\n
mysql> explain select id_channel from channel limit 398800,100;\r\n+----+-------------+---------+-------+---------------+---------+---------+------+--------+-------------+\r\n| id | select_type | table   | type  | possible_keys | key     | key_len | ref  | rows   | Extra       |\r\n+----+-------------+---------+-------+---------------+---------+---------+------+--------+-------------+\r\n|  1 | SIMPLE      | channel | index | NULL          | PRIMARY | 8       | NULL | 722583 | Using index |\r\n+----+-------------+---------+-------+---------------+---------+---------+------+--------+-------------+\r\n1 row in set (0.00 sec)<\/code><\/pre>\n
mysql> explain select payload from channel where id_channel in (select id_channel from channel_tmp);\r\n+----+--------------+-------------+-------------+---------------+---------+---------+------+--------+-------------+\r\n| id | select_type  | table       | type            | poss_keys | key     | key_len | ref  | rows   | Extra       |\r\n+----+--------------+-------------+-------------+---------------+---------+---------+------+--------+-------------+\r\n|  1 | PRIMARY      | channel     | ALL             | NULL      | NULL    | NULL    | NULL | 722327 | Using where |\r\n|  2 | DEP SUBQUERY | channel_tmp | unique_subquery | PRIMARY   | PRIMARY | 8       | func |      1 | Using index |\r\n+----+--------------+-------------+-------------+---------------+---------+---------+------+--------+-------------+\r\n2 rows in set (0.00 sec)<\/code><\/pre>\n
Let’s try a self-join doing all of the above without explicitly making a temporary table. Self-joins can be pretty powerful – neat in the right places..<\/p>\n
mysql> explain SELECT payload\r\n  FROM channel c1,\r\n       (SELECT id_channel FROM channel limit 398800,100) c2\r\n  WHERE c1.id_channel=c2.id_channel;\r\n+----+-------------+------------+--------+---------------+---------+---------+---------------+--------+-------------+\r\n| id | select_type | table      | type   | possible_keys | key     | key_len | ref           | rows   | Extra       |\r\n+----+-------------+------------+--------+---------------+---------+---------+---------------+--------+-------------+\r\n|  1 | PRIMARY     | derived2   | ALL    | NULL          | NULL    | NULL    | NULL          |    100 |             |\r\n|  1 | PRIMARY     | c1         | eq_ref | PRIMARY       | PRIMARY | 8       | c2.id_channel |      1 |             |\r\n|  2 | DERIVED     | channel    | index  | NULL          | PRIMARY | 8       | NULL          | 721559 | Using index |\r\n+----+-------------+------------+--------+---------------+---------+---------+---------------+--------+-------------+\r\n3 rows in set (0.21 sec)<\/code><\/pre>\n
This pulls out the right rows and even works around the “no limit in subselect” unsupported mysql feature but that id_channel selection in c2 still isn’t quite doing the right thing – I don’t like all the rows being returned, even if they’re coming straight out of the primary key index.<\/p>\n
A little bit of rudimentary benchmarking appears to suggest that the self-join is the fastest, followed by the original query at approximately one order of magnitude slower and trailing a long way behind at around another four-times slower than that, the temporary table. I’m not sure how or why the temporary table performance happens to be the slowest – perhaps down to storage access, or more likely my lack of understanding. Some time I might even try the in-memory table too for comparison.<\/p>\n","protected":false},"excerpt":{"rendered":"
Background: I have a big table called “channel” with a few hundred thousand rows in – nothing vast, but big enough to cause some queries to run slower than I want. Today I was fixing something else and happened to run show full processlist; I noticed this taking too long: SELECT payload FROM channel LIMIT … Continue reading “A Little SQL “EXPLAIN” example”<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"footnotes":""},"categories":[58,11],"tags":[1086,16,14,15],"class_list":["post-194","post","type-post","status-publish","format-standard","hentry","category-databases","category-programming","tag-databases","tag-explain","tag-optimisation","tag-sql"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/psyphi.net\/blog\/wp-json\/wp\/v2\/posts\/194","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/psyphi.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/psyphi.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/psyphi.net\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/psyphi.net\/blog\/wp-json\/wp\/v2\/comments?post=194"}],"version-history":[{"count":21,"href":"https:\/\/psyphi.net\/blog\/wp-json\/wp\/v2\/posts\/194\/revisions"}],"predecessor-version":[{"id":215,"href":"https:\/\/psyphi.net\/blog\/wp-json\/wp\/v2\/posts\/194\/revisions\/215"}],"wp:attachment":[{"href":"https:\/\/psyphi.net\/blog\/wp-json\/wp\/v2\/media?parent=194"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/psyphi.net\/blog\/wp-json\/wp\/v2\/categories?post=194"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/psyphi.net\/blog\/wp-json\/wp\/v2\/tags?post=194"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}