Nuxwin Nuxwin - 4 months ago 17
SQL Question

MySQL - Covering index - Range query

Right now, I've a SQL query such as:

SELECT
SUM(dtraff_web) AS dtraff_web,
SUM(dtraff_ftp) AS dtraff_ftp,
SUM(dtraff_mail) AS dtraff_mail,
SUM(dtraff_pop) AS dtraff_pop
FROM domain_traffic WHERE `dtraff_time` BETWEEN
UNIX_TIMESTAMP(((LAST_DAY(CURDATE()) + INTERVAL 1 DAY) - INTERVAL 1 MONTH ))
AND
UNIX_TIMESTAMP((LAST_DAY(CURDATE()) + INTERVAL 1 DAY ))
AND
domain_id = ?


I've added an index which covers all fields (covering index) such as:

ALTER TABLE `domain_traffic` ADD INDEX `full_covering` (`domain_id`, `dtraff_time`, `dtraff_web`, `dtraff_ftp`, `dtraff_mail`, `dtraff_pop`) USING BTREE;


to avoid full scan but when explaining the query I get: 'Using where; Using index'

I would avoid 'Using where'. It is possible when range is involved?

Current `explain' result is:

mysql> EXPLAIN SELECT SQL_NO_CACHE
-> SUM(dtraff_web) AS dtraff_web,
-> SUM(dtraff_ftp) AS dtraff_ftp,
-> SUM(dtraff_mail) AS dtraff_mail,
-> SUM(dtraff_pop) AS dtraff_pop
-> FROM domain_traffic WHERE `dtraff_time` BETWEEN
-> UNIX_TIMESTAMP(((LAST_DAY(CURDATE()) + INTERVAL 1 DAY) - INTERVAL 1 MONTH ))
-> AND
-> UNIX_TIMESTAMP((LAST_DAY(CURDATE()) + INTERVAL 1 DAY ))
-> AND
-> domain_id = 1;
+----+-------------+----------------+------------+-------+----------------------------------+---------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------------+------------+-------+----------------------------------+---------------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | domain_traffic | NULL | range | i_unique_timestamp,full_covering | full_covering | 12 | NULL | 959 | 100.00 | Using where; Using index |
+----+-------------+----------------+------------+-------+----------------------------------+---------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0,00 sec)

mysql>


Table schema is:

mysql> show create table domain_traffic;
+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| domain_traffic | CREATE TABLE `domain_traffic` (
`dtraff_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`domain_id` int(10) unsigned NOT NULL,
`dtraff_time` bigint(20) unsigned NOT NULL,
`dtraff_web` bigint(20) unsigned DEFAULT '0',
`dtraff_ftp` bigint(20) unsigned DEFAULT '0',
`dtraff_mail` bigint(20) unsigned DEFAULT '0',
`dtraff_pop` bigint(20) unsigned DEFAULT '0',
PRIMARY KEY (`dtraff_id`),
UNIQUE KEY `i_unique_timestamp` (`domain_id`,`dtraff_time`),
KEY `full_covering` (`domain_id`,`dtraff_time`,`dtraff_web`,`dtraff_ftp`,`dtraff_mail`,`dtraff_pop`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=5452211 DEFAULT CHARSET=latin1 |
+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0,00 sec)

mysql>

Answer

I don't put a ton of stock in Explain. It's a clue, but it is still voodoo. The same explain output from two similar queries can return the same Explain output, yet different performance (reference forthcoming). Which means the Optimizer rules.

The insert routine below is used for anyone that wants to take it from here. Without adding adequate size to a test table, the index is not used for queries. The manual states that of course, just saying. Because some people think indexes are always used if they seem like they should be (ie: a guy's test table with 10 rows won't be using indexes).

Below is some chicken scratch. Hopefully it is helpful.

create schema nuxwin099a;
use nuxwin099a;

CREATE TABLE `domain_traffic` (
  `dtraff_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `domain_id` int(10) unsigned NOT NULL,
  `dtraff_time` bigint(20) unsigned NOT NULL,
  `dtraff_web` bigint(20) unsigned DEFAULT '0',
  `dtraff_ftp` bigint(20) unsigned DEFAULT '0',
  `dtraff_mail` bigint(20) unsigned DEFAULT '0',
  `dtraff_pop` bigint(20) unsigned DEFAULT '0',
  PRIMARY KEY (`dtraff_id`),
  UNIQUE KEY `i_unique_timestamp` (`domain_id`,`dtraff_time`),
  KEY `full_covering` (`domain_id`,`dtraff_time`,`dtraff_web`,`dtraff_ftp`,`dtraff_mail`,`dtraff_pop`) USING BTREE
) ENGINE=InnoDB;

Stored proc to insert random rows:

drop procedure if exists insertMany;
DELIMITER $$
create procedure insertMany
(
    howMany int
)
BEGIN
    DECLARE soFar int default 0;
    WHILE soFar<howMany DO
        -- insert ignore is used because of `i_unique_timestamp`
        insert ignore domain_traffic (domain_id,dtraff_time,dtraff_web,dtraff_ftp,dtraff_mail,dtraff_pop) values
        (1,rand()*2000000+unix_timestamp(now())-3000000,1,2,3,4);

        set soFar=soFar+1;
        if soFar>500 then
            set soFar=howMany; -- hah, you can't trick me. I am not doing that many. RAND() is slow.
        end if;
    END WHILE;
END$$
DELIMITER ; 

Test Stored Proc:

call insertMany(1000);
-- select count(*) from domain_traffic;

.

select from_unixtime(dtraff_time) from domain_traffic;
select 60*60*24*30;
--  2.6M sec/month

1 469 124 890
1 469 125 020
1 469 125 042
select unix_timestamp(now()); -- seconds since epoch
select current_timestamp();

Test some explain output (3 conditions):

Stub A:
WHERE `dtraff_time` BETWEEN 1 and 2 

Stub B:
WHERE `dtraff_time` BETWEEN 1400000000 and 1500000000 

Stub C:
WHERE `dtraff_time` BETWEEN UNIX_TIMESTAMP(((LAST_DAY(CURDATE()) + INTERVAL 1 DAY) - INTERVAL 1 MONTH )) and UNIX_TIMESTAMP((LAST_DAY(CURDATE()) + INTERVAL 1 DAY ))

EXPLAIN SELECT  
SUM(dtraff_web) AS dtraff_web, 
SUM(dtraff_ftp) AS dtraff_ftp, 
SUM(dtraff_mail) AS dtraff_mail, 
SUM(dtraff_pop) AS dtraff_pop 
FROM domain_traffic 
WHERE (fill in stubs A to C above, individually)  
AND domain_id = 1; 

Explain results, Stub A:
+----+-------------+----------------+-------+----------------------------------+--------------------+---------+------+------+-----------------------+
| id | select_type | table          | type  | possible_keys                    | key                | key_len | ref  | rows | Extra                 |
+----+-------------+----------------+-------+----------------------------------+--------------------+---------+------+------+-----------------------+
|  1 | SIMPLE      | domain_traffic | range | i_unique_timestamp,full_covering | i_unique_timestamp | 12      | NULL |    1 | Using index condition |
+----+-------------+----------------+-------+----------------------------------+--------------------+---------+------+------+-----------------------+

Explain results, Stub B:
+----+-------------+----------------+-------+----------------------------------+---------------+---------+------+------+--------------------------+
| id | select_type | table          | type  | possible_keys                    | key           | key_len | ref  | rows | Extra                    |
+----+-------------+----------------+-------+----------------------------------+---------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | domain_traffic | range | i_unique_timestamp,full_covering | full_covering | 12      | NULL | 1284 | Using where; Using index |
+----+-------------+----------------+-------+----------------------------------+---------------+---------+------+------+--------------------------+

Explain results, Stub C:
+----+-------------+----------------+-------+----------------------------------+---------------+---------+------+------+--------------------------+
| id | select_type | table          | type  | possible_keys                    | key           | key_len | ref  | rows | Extra                    |
+----+-------------+----------------+-------+----------------------------------+---------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | domain_traffic | range | i_unique_timestamp,full_covering | full_covering | 12      | NULL |  515 | Using where; Using index |
+----+-------------+----------------+-------+----------------------------------+---------------+---------+------+------+--------------------------+

Cleanup:

drop schema nuxwin099a;

What matters to me most is performance, versus fixating over the exact Explain output.

Comments