mysql> select count(*) from phpcms_ads_stat;
+----------+
| count(*) |
+----------+
| 9345149 |
+----------+
1 row in set (0.00 sec)mysql> show index from phpcms_ads_stat;
+-----------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| phpcms_ads_stat | 0 | PRIMARY | 1 | id | A | 9345149 | NULL | NULL | | BTREE | |
| phpcms_ads_stat | 1 | adsid | 1 | adsid | A | 588 | NULL | NULL | | BTREE | |
| phpcms_ads_stat | 1 | adsid | 2 | type | A | 588 | NULL | NULL | | BTREE | |
| phpcms_ads_stat | 1 | adsid | 3 | ip | A | 4672574 | NULL | NULL | | BTREE | |
| phpcms_ads_stat | 1 | clicktime | 1 | clicktime | A | 4672574 | NULL | NULL | | BTREE | |
+-----------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
5 rows in set (0.00 sec)mysql> explain SELECT *, COUNT(*) AS num FROM phpcms_ads_stat WHERE adsid=114 AND type=1 GROUP BY area ORDER BY num DESC;
+----+-------------+-----------------+------+---------------+-------+---------+-------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------+------+---------------+-------+---------+-------------+------+----------------------------------------------+
| 1 | SIMPLE | phpcms_ads_stat | ref | adsid | adsid | 4 | const,const | 7696 | Using where; Using temporary; Using filesort |
+----+-------------+-----------------+------+---------------+-------+---------+-------------+------+----------------------------------------------+
1 row in set (0.00 sec)mysql> 想知道这个查询语句还需要怎么优化?这个查询要20几秒
+----------+
| count(*) |
+----------+
| 9345149 |
+----------+
1 row in set (0.00 sec)mysql> show index from phpcms_ads_stat;
+-----------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| phpcms_ads_stat | 0 | PRIMARY | 1 | id | A | 9345149 | NULL | NULL | | BTREE | |
| phpcms_ads_stat | 1 | adsid | 1 | adsid | A | 588 | NULL | NULL | | BTREE | |
| phpcms_ads_stat | 1 | adsid | 2 | type | A | 588 | NULL | NULL | | BTREE | |
| phpcms_ads_stat | 1 | adsid | 3 | ip | A | 4672574 | NULL | NULL | | BTREE | |
| phpcms_ads_stat | 1 | clicktime | 1 | clicktime | A | 4672574 | NULL | NULL | | BTREE | |
+-----------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
5 rows in set (0.00 sec)mysql> explain SELECT *, COUNT(*) AS num FROM phpcms_ads_stat WHERE adsid=114 AND type=1 GROUP BY area ORDER BY num DESC;
+----+-------------+-----------------+------+---------------+-------+---------+-------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------+------+---------------+-------+---------+-------------+------+----------------------------------------------+
| 1 | SIMPLE | phpcms_ads_stat | ref | adsid | adsid | 4 | const,const | 7696 | Using where; Using temporary; Using filesort |
+----+-------------+-----------------+------+---------------+-------+---------+-------------+------+----------------------------------------------+
1 row in set (0.00 sec)mysql> 想知道这个查询语句还需要怎么优化?这个查询要20几秒
解决方案 »
- myql服务器启动不了
- 一个SQL语句
- 一个纠结的查询,帮个忙!
- LOAD DATA INFILE 语句错误
- MYSQL 自定义函数问题!!!!!!!!!!!!!!!!!!!!!!!!!!!
- mysqlimport能导入表结构,存储过程,存储方法吗?
- mysql与sql server的链接问题
- MYSQL 有没有象MSSQL那样的性能监控器
- 程序中怎样调用mySql的存储过程并取得输出参数?????????????
- MySQL数据库意外停止,每天都要重启,求解决
- 怎样确保获取完整的相邻的重复记录--相邻的两行,某列具有相同的值,否则被认为隔断了
- north表记录源于south表中的数据进行计算,用存储过程实现
explain SELECT *, COUNT(*) AS num FROM phpcms_ads_stat WHERE adsid=114 AND type=1 GROUP BY area ORDER BY num DESC;
+-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| phpcms_ads_stat | 0 | PRIMARY | 1 | id | A | 3588426 | NULL | NULL | | BTREE | |
| phpcms_ads_stat | 1 | adsid | 1 | adsid | A | 271 | NULL | NULL | | BTREE | |
| phpcms_ads_stat | 1 | adsid | 2 | type | A | 271 | NULL | NULL | | BTREE | |
| phpcms_ads_stat | 1 | adsid | 3 | ip | A | 1794213 | NULL | NULL | | BTREE | |
| phpcms_ads_stat | 1 | area | 1 | area | A | 474 | NULL | NULL | | BTREE | |
| phpcms_ads_stat | 1 | ata | 1 | adsid | A | 271 | NULL | NULL | | BTREE | |
| phpcms_ads_stat | 1 | ata | 2 | type | A | 271 | NULL | NULL | | BTREE | |
| phpcms_ads_stat | 1 | ata | 3 | area | A | 3156 | NULL | NULL | | BTREE | |
+-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
8 rows in set (0.02 sec)mysql> explain SELECT *, COUNT(*) AS num FROM phpcms_ads_stat WHERE adsid=114 AND type=1 GROUP BY area ORDER BY num DESC;
+----+-------------+-----------------+------+---------------+------+---------+-------------+-------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------+------+---------------+------+---------+-------------+-------+----------------------------------------------+
| 1 | SIMPLE | phpcms_ads_stat | ref | adsid,ata | ata | 4 | const,const | 14868 | Using where; Using temporary; Using filesort |
+----+-------------+-----------------+------+---------------+------+---------+-------------+-------+----------------------------------------------+
1 row in set (0.00 sec)
在3588426 条记录中,有14868 多条符合要求。然后你的排序是以COUNT(*)排的,只能产生结果集后再进行排序Using filesort。