0

EDITED: Can you tell me which explain plan is better?!

First:

explain SELECT SQL_NO_CACHE count(*) FROM a force index(uni) WHERE a.date BETWEEN 20141001 AND 20141031 AND ((a.cid = 157 AND a.tid IN ( 828,32,2174,1539,520,1026,1004,16,44,55,1393,84,512,161,517,424,2198,1693,20,1789)) ) GROUP BY a.date,a.cid,a.tid,a.mid,a.cmid HAVING 2>1 LIMIT 999999;
+----+-------------+-------+-------+---------------+------+---------+------+--------+--------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows   | Extra                    |
+----+-------------+-------+-------+---------------+------+---------+------+--------+--------------------------+
|  1 | SIMPLE      | a     | range | uni           | uni  | 11      | NULL | 116661 | Using where; Using index |
+----+-------------+-------+-------+---------------+------+---------+------+--------+--------------------------+
1 row in set (0.22 sec)

Second:

explain select SQL_NO_CACHE count(*) FROM a WHERE a.date BETWEEN 20141001 AND 20141031 AND ((a.cid = 157 AND a.tid IN (828,32,2174,1539,520,1026,1004,16,44,55,1393,84,512,161,517,424,2198,1693,20,1789)) ) GROUP BY a.cmid,a.mid,a.date,a.tid,a.cid HAVING  2>1 LIMIT 999999;
+----+-------------+-------+-------+--------------------------------------------+------------------+---------+------+-------+----------------------------------------------+
| id | select_type | table | type  | possible_keys                              | key              | key_len | ref  | rows  | Extra                                        |
+----+-------------+-------+-------+--------------------------------------------+------------------+---------+------+-------+----------------------------------------------+
|  1 | SIMPLE      | a     | range | uni,cid_tgid_mid,cid_tgid_cmid | cid_tgid_mid | 8       | NULL | 15203 | Using where; Using temporary; Using filesort |
+----+-------------+-------+-------+--------------------------------------------+------------------+---------+------+-------+----------------------------------------------+
1 row in set (0.00 sec)

Table a schema:

show create table a \G
*************************** 1. row ***************************
       Table: a
Create Table: CREATE TABLE `a` (
  `aid` int(11) NOT NULL AUTO_INCREMENT,
  `date` date NOT NULL,
  `cid` int(11) NOT NULL,
  `tid` int(11) NOT NULL,
  `mid` int(11) DEFAULT NULL,
  `cmid` int(11) DEFAULT NULL,
  `view_count` int(11) DEFAULT NULL,
  `view_tcinline_sum` float(10,3) DEFAULT NULL,
  `view_tcinline_count` int(11) DEFAULT NULL,
  `c_count` int(11) DEFAULT NULL,
  PRIMARY KEY (`aid`),
  UNIQUE KEY `uni` (`date`,`cid`,`tid`,`mid`,`cmid`),
  KEY `cid_tgid_mid` (`cid`,`tid`,`mid`,`date`),
  KEY `cid_tgid_cmid` (`cid`,`tid`,`cmid`,`date`)
) ENGINE=MyISAM AUTO_INCREMENT=15016573 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

For the first query I forced mysql to use unique index but I got 122431 examined. In the second explain query mysql used a index and it created a temporary table but there is less rows examined.

Can you help me?

  • Please post the query and the SHOW CREATE TABLE tblname\G – RolandoMySQLDBA Jan 7 '15 at 15:43
  • Remember, when trying to compare performance between variant queries, you need to use SELECT SQL_NO_CACHE so that you get a better sense of how long the server actually takes to process them. 0.00 sec seems pretty low. – Michael - sqlbot Jan 8 '15 at 1:14
  • You may try to save the temporary tables in RAM, give it a try moiseevigor.github.io/software/2015/01/23/…, at least you'll understand the speed difference with the second scenario – Igor Jan 24 '15 at 19:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.