right now, i've sql query such as:
select sum(dtraff_web) dtraff_web, sum(dtraff_ftp) dtraff_ftp, sum(dtraff_mail) dtraff_mail, sum(dtraff_pop) dtraff_pop domain_traffic `dtraff_time` between unix_timestamp(((last_day(curdate()) + interval 1 day) - interval 1 month )) , unix_timestamp((last_day(curdate()) + interval 1 day )) , domain_id = ? i've added index covers 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 when explaining query get: 'using where; using index'
i avoid 'using where'. possible when range involved?
current `explain' result is:
mysql> explain select sql_no_cache -> sum(dtraff_web) dtraff_web, -> sum(dtraff_ftp) dtraff_ftp, -> sum(dtraff_mail) dtraff_mail, -> sum(dtraff_pop) dtraff_pop -> domain_traffic `dtraff_time` between -> unix_timestamp(((last_day(curdate()) + interval 1 day) - interval 1 month )) -> , -> unix_timestamp((last_day(curdate()) + interval 1 day )) -> , -> domain_id = 1; +----+-------------+----------------+------------+-------+----------------------------------+---------------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | | +----+-------------+----------------+------------+-------+----------------------------------+---------------+---------+------+------+----------+--------------------------+ | 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>
i don't put ton of stock in explain. it's clue, still voodoo. same explain output 2 similar queries can return same explain output, yet different performance (reference forthcoming). means optimizer rules.
the insert routine below used wants take here. without adding adequate size test table, index not used queries. manual states of course, saying. because people think indexes used if seem should (ie: guy's test table 10 rows won't using indexes).
below chicken scratch. 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 insert random rows:
drop procedure if exists insertmany; delimiter $$ create procedure insertmany ( howmany int ) begin declare sofar int default 0; while sofar<howmany -- insert ignore 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 set sofar=howmany; -- hah, can't trick me. not doing many. rand() slow. end if; end while; end$$ delimiter ; test stored proc:
call insertmany(1000); -- select count(*) domain_traffic; .
select from_unixtime(dtraff_time) 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 explain output (3 conditions):
stub a: `dtraff_time` between 1 , 2 stub b: `dtraff_time` between 1400000000 , 1500000000 stub c: `dtraff_time` between unix_timestamp(((last_day(curdate()) + interval 1 day) - interval 1 month )) , unix_timestamp((last_day(curdate()) + interval 1 day )) explain select sum(dtraff_web) dtraff_web, sum(dtraff_ftp) dtraff_ftp, sum(dtraff_mail) dtraff_mail, sum(dtraff_pop) dtraff_pop domain_traffic (fill in stubs c above, individually) , domain_id = 1; explain results, stub a: +----+-------------+----------------+-------+----------------------------------+--------------------+---------+------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | | +----+-------------+----------------+-------+----------------------------------+--------------------+---------+------+------+-----------------------+ | 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 | | +----+-------------+----------------+-------+----------------------------------+---------------+---------+------+------+--------------------------+ | 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 | | +----+-------------+----------------+-------+----------------------------------+---------------+---------+------+------+--------------------------+ | 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 me performance, versus fixating on exact explain output.
Comments
Post a Comment