Need help understanding how mysql indexes work

Question

I have a table that looks like this:

CREATE TABLE `metric` ( `metricid` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `host` varchar(50) NOT NULL, `userid` int(10) unsigned DEFAULT NULL, `lastmetricvalue` double DEFAULT NULL, `receivedat` int(10) unsigned DEFAULT NULL, `name` varchar(255) NOT NULL, `sampleid` tinyint(3) unsigned NOT NULL, `type` tinyint(3) unsigned NOT NULL DEFAULT '0', `lastrawvalue` double NOT NULL, `priority` tinyint(3) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`metricid`), UNIQUE KEY `unique-metric` (`userid`,`host`,`name`,`sampleid`) ) ENGINE=InnoDB AUTO_INCREMENT=1000000221496 DEFAULT CHARSET=utf8 

It currently has 178,892 rows, and when I run the following query:

 select metricid, lastrawvalue, receivedat, name, sampleid FROM metric m WHERE m.userid = 8 AND (host, name, sampleid) IN (('localhost','0.4350799184758216cpu-3/cpu-nice',0), ('localhost','0.4350799184758216cpu-3/cpu-system',0), ('localhost','0.4350799184758216cpu-3/cpu-idle',0), ('localhost','0.4350799184758216cpu-3/cpu-wait',0), ('localhost','0.4350799184758216cpu-3/cpu-interrupt',0), ('localhost','0.4350799184758216cpu-3/cpu-softirq',0), ('localhost','0.4350799184758216cpu-3/cpu-steal',0), ('localhost','0.4350799184758216cpu-4/cpu-user',0), ('localhost','0.4350799184758216cpu-4/cpu-nice',0), ('localhost','0.4350799184758216cpu-4/cpu-system',0), ('localhost','0.4350799184758216cpu-4/cpu-idle',0), ('localhost','0.4350799184758216cpu-4/cpu-wait',0), ('localhost','0.4350799184758216cpu-4/cpu-interrupt',0), ('localhost','0.4350799184758216cpu-4/cpu-softirq',0), ('localhost','0.4350799184758216cpu-4/cpu-steal',0), ('localhost','_util/billing-bytes',0),('localhost','_util/billing-metrics',0)); 

0.87 seconds are required to return the results, explain:

  *************************** 1. row *************************** id: 1 select_type: SIMPLE table: m type: ref possible_keys: unique-metric key: unique-metric key_len: 5 ref: const rows: 85560 Extra: Using where 1 row in set (0.00 sec) 

Profile

as follows:

 +--------------------------------+----------+ | Status | Duration | +--------------------------------+----------+ | starting | 0.000160 | | checking permissions | 0.000010 | | Opening tables | 0.000021 | | exit open_tables() | 0.000008 | | System lock | 0.000008 | | mysql_lock_tables(): unlocking | 0.000005 | | exit mysqld_lock_tables() | 0.000007 | | init | 0.000068 | | optimizing | 0.000018 | | statistics | 0.000091 | | preparing | 0.000042 | | executing | 0.000005 | | Sending data | 0.870180 | | innobase_commit_low():trx_comm | 0.000012 | | Sending data | 0.000111 | | end | 0.000009 | | query end | 0.000009 | | ha_commit_one_phase(-1) | 0.000015 | | innobase_commit_low():trx_comm | 0.000004 | | ha_commit_one_phase(-1) | 0.000005 | | query end | 0.000005 | | closing tables | 0.000012 | | freeing items | 0.000562 | | logging slow query | 0.000005 | | cleaning up | 0.000005 | | sleeping | 0.000006 | +--------------------------------+----------+ 

Which seems too tall for me. I tried to replace the userid = 8 and (host, name, sampleid) IN first request with (userid, host, name, sampleid) IN , and this request works for about 0.5 s - almost 2 times faster, for reference, here request:

 select metricid, lastrawvalue, receivedat, name, sampleid FROM metric m WHERE (userid, host, name, sampleid) IN ((8,'localhost','0.4350799184758216cpu-3/cpu-nice',0), (8,'localhost','0.4350799184758216cpu-3/cpu-system',0), (8,'localhost','0.4350799184758216cpu-3/cpu-idle',0), (8,'localhost','0.4350799184758216cpu-3/cpu-wait',0), (8,'localhost','0.4350799184758216cpu-3/cpu-interrupt',0), (8,'localhost','0.4350799184758216cpu-3/cpu-softirq',0), (8,'localhost','0.4350799184758216cpu-3/cpu-steal',0), (8,'localhost','0.4350799184758216cpu-4/cpu-user',0), (8,'localhost','0.4350799184758216cpu-4/cpu-nice',0), (8,'localhost','0.4350799184758216cpu-4/cpu-system',0), (8,'localhost','0.4350799184758216cpu-4/cpu-idle',0), (8,'localhost','0.4350799184758216cpu-4/cpu-wait',0), (8,'localhost','0.4350799184758216cpu-4/cpu-interrupt',0), (8,'localhost','0.4350799184758216cpu-4/cpu-softirq',0), (8,'localhost','0.4350799184758216cpu-4/cpu-steal',0), (8,'localhost','_util/billing-bytes',0), (8,'localhost','_util/billing-metrics',0)); 

his explanation is as follows:

 *************************** 1. row *************************** id: 1 select_type: SIMPLE table: m type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 171121 Extra: Using where 1 row in set (0.00 sec) 

Next, I updated the table to contain one merged column:

 alter table `metric` add `forindex` varchar(120) not null default ''; update metric set forindex = concat(userid,`host`,`name`,sampleid); alter table metric add index `forindex` (`forindex`); 

Updated query to search only 1 line:

 select metricid, lastrawvalue, receivedat, name, sampleid FROM metric m WHERE (forindex) IN (('8localhost0.4350799184758216cpu-3/cpu-nice0'), ('8localhost0.4350799184758216cpu-3/cpu-system0'), ('8localhost0.4350799184758216cpu-3/cpu-idle0'), ('8localhost0.4350799184758216cpu-3/cpu-wait0'), ('8localhost0.4350799184758216cpu-3/cpu-interrupt0'), ('8localhost0.4350799184758216cpu-3/cpu-softirq0'), ('8localhost0.4350799184758216cpu-3/cpu-steal0'), ('8localhost0.4350799184758216cpu-4/cpu-user0'), ('8localhost0.4350799184758216cpu-4/cpu-nice0'), ('8localhost0.4350799184758216cpu-4/cpu-system0'), ('8localhost0.4350799184758216cpu-4/cpu-idle0'), ('8localhost0.4350799184758216cpu-4/cpu-wait0'), ('8localhost0.4350799184758216cpu-4/cpu-interrupt0'), ('8localhost0.4350799184758216cpu-4/cpu-softirq0'), ('8localhost0.4350799184758216cpu-4/cpu-steal0'), ('8localhost_util/billing-bytes0'), ('8localhost_util/billing-metrics0')); 

And now I get the same results in 0.00 seconds! Explain:

 *************************** 1. row *************************** id: 1 select_type: SIMPLE table: m type: range possible_keys: forindex key: forindex key_len: 362 ref: NULL rows: 17 Extra: Using where 1 row in set (0.00 sec) 

So, to summarize, here are the results:

• m.userid = X AND (host, name, sampleid) IN - the index is used, 85560 lines are scanned, work in 0.9 s
• (userid, host, name, sampleid) IN - the index is not used , 171121 lines are scanned, launched in 0.5 s
• an additional column with a composite index is replaced by an index on the combined utility column - an index is used, 17 rows are checked, launched at 0s

Why is the second query faster than the first? And why is the third request so much faster than the rest? Should I keep such a column for the sole purpose of a faster search?

Mysql version: mysqld Ver 5.5.34-55 for Linux on x86_64 (Percona XtraDB Cluster (GPL), wsrep_25.9.r3928)

Answer 1

Indexes help search queries in the WHERE by narrowing the search as much as possible. You can see how this happens ...

The rows EXPLAIN field gives an estimate of how many rows the query will need to examine to find rows matching your query. Comparing the rows reported in each EXPLAIN, you can see how much better your optimized query is:

  rows: 85560 -- first query rows: 171121 -- second query examines 2x more rows, but it was probably -- faster because the data was buffered after the first query rows: 17 -- third query examines 5,000x fewer rows than first query 

You would also notice SHOW PROFILE in detail if you ran this for the third query that “Sending data” is much faster for a faster query. This process state indicates how long it took to copy the rows from the storage engine to the MySQL SQL level. Even when copying memory to memory, it takes some time for so many thousands of lines. This is why indexes are so useful.

For a more useful explanation, see my presentation How to Create Indexes, Really .