I created a table partitioned by a numerical identifier:
CREATE TABLE mytable (
...
`id` int(11) DEFAULT NULL
...
) ENGINE=InnoDB DEFAULT CHARSET=latin1 PARTITION BY HASH (`id`) PARTITIONS 100
I do not have a primary key, but there are a number of indexes. I have no data in my table where id is less than 0 or greater than 30 (for now I expect this to grow). Most of my queries first include an identifier to reduce the search space.
I decided that the query to select distinct(id) from mytablewould simply return the number of sections in which the data was. I was surprised that an explanation about this instead completely scans the data:
explain partitions select distinct(id) from mytable;
| 1 | SIMPLE | mytable | p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,p13,p14,p15,p16,p17,p18,p19,p20,p21,p22,p23,p24,p25,p26,p27,p28,p29,p30,p31,p32,p33,p34,p35,p36,p37,p38,p39,p40,p41,p42,p43,p44,p45,p46,p47,p48,p49,p50,p51,p52,p53,p54,p55,p56,p57,p58,p59,p60,p61,p62,p63,p64,p65,p66,p67,p68,p69,p70,p71,p72,p73,p74,p75,p76,p77,p78,p79,p80,p81,p82,p83,p84,p85,p86,p87,p88,p89,p90,p91,p92,p93,p94,p95,p96,p97,p98,p99 | ALL | NULL | NULL | NULL | NULL | 24667132 | Using temporary |
explain select distinct(id) from mytable;
+----+-------------+----------------------+------+---------------+------+---------+------+----------+-----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------------+------+---------------+------+---------+------+----------+-----------------+
| 1 | SIMPLE | mytable | ALL | NULL | NULL | NULL | NULL | 24667132 | Using temporary |
+----+-------------+----------------------+------+---------------+------+---------+------+----------+-----------------+
Then, I will read the https://stackoverflow.com/a/3123169/ answer, which explained how the MySQL function works hash().
: MySQL , , ( select select() )?
Server version: 5.5.35-0ubuntu0.12.04.2 (Ubuntu).