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?
Aucun commentaire:
Enregistrer un commentaire