解决方案 »
- 关于mysql-cluster用于实际生产环境的问题
- mysql下 group by、sum 语句占CPU高问题!@mysql版主啊!!~
- 求sql语句,有难度的。谢谢
- mysql_query($sql);
- 怎么随机取表里面的某一条记录
- Mysql-proxy 读写分离 多连接时,ERROR 1105 (07000): can't change DB to on slave 192.168.0.9:3306
- MYSQL数据库安装问题
- 请教怎样把SQL2000数据库导出成.csv文件????请问详细步骤,而且不要丢数据~~~
- postgreSQL怎么样控制用户的权限?
- 请教一下字符集设置问题。
- csdn的能人帮我解决个mysql配置问题.急!!
- 想请教下,这条SQL语句有什么问题
没有
在上个帖子就说过,GROUP BY是最快的
+----------+
| count(*) |
+----------+
| 1000000 |
+----------+
1 row in set (0.00 sec)mysql> select count(1) from (select 1 from a where a3 between '2010-01-01 00:00:
00' and '2010-12-31 23:59:59' group by a1,a2) tmp;
+----------+
| count(1) |
+----------+
| 99813 |
+----------+
1 row in set (2.08 sec)mysql> select count(distinct a1,a2) from a where a3 between '2010-01-01 00:00:00
' and '2010-12-31 23:59:59';
+-----------------------+
| count(distinct a1,a2) |
+-----------------------+
| 99813 |
+-----------------------+
1 row in set (2.55 sec)mysql>
mysql> create index bbb on a(a3);
Query OK, 1000000 rows affected (23.03 sec)
Records: 1000000 Duplicates: 0 Warnings: 0mysql> select count(1) from (select 1 from a where a3 between '2010-01-01 00:00:
00' and '2010-12-31 23:59:59' group by a1,a2) tmp;
+----------+
| count(1) |
+----------+
| 99813 |
+----------+
1 row in set (1.50 sec)mysql> select count(distinct a1,a2) from a where a3 between '2010-01-01 00:00:00
' and '2010-12-31 23:59:59';
+-----------------------+
| count(distinct a1,a2) |
+-----------------------+
| 99813 |
+-----------------------+
1 row in set (1.17 sec)mysql>
SQL2执行时间:1.496s只是第一条SQL出现了 Using where; Using temporary; Using filesort。wwwwb,如果使用group by,可以对SQL1进行优化吗?
看你的执行结果, 电脑挺牛的啊。我还是双核电脑,2G内存, 加个索引就卡死了。
既然group by 不快, 就不用了
sql 1: 0.875s Using where; Using index; Using temporary; Using filesort
sql 2: 0.735s Using where; Using index