mysql下
select * from log group by Id order by LoginTime
这个语句需要建立索引
我建立2个索引 分别是id 和 LoginTime
有人说建立复合索引 (id,LoginTime)我仍选择2个索引 只是不知道明确的原因???
select * from log group by Id order by LoginTime
这个语句需要建立索引
我建立2个索引 分别是id 和 LoginTime
有人说建立复合索引 (id,LoginTime)我仍选择2个索引 只是不知道明确的原因???
解决方案 »
- MySql插入乱码
- MYSQL中,关于用户的权限的问题。
- 按照IT经验翻译过来的,对不?
- 结贴结快了。发现还是有问题
- 请教一个读取日期的问题
- 求函数?读取所选数据的行数
- 怎样用存储过程动态创建表????????????????????????????????????????
- 告急! 请问我的MYSQL安装在C盘,如何才能设置我的数据库文件存放别的盘下如D盘下的DATE下~~~
- You have an error in your SQL syntax; check the manual that corresponds to your
- 实体类设计
- mysql 如何只优化一个分区?
- 语句不走索引(复合字段索引)
请注意是
group by Id order by LoginTime 不是 where id=? and LoginTime =?好像说不通?
在按loginTime排序数据量大的时候和数据量小的时候 建立索引两种方式效率是不同的
也走了索引的
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| a | 0 | PRIMARY | 1 | id | A | 2 | NULL | NULL | | BTREE | |
| a | 1 | ids_ab | 1 | a | A | 2 | NULL | NULL | YES | BTREE | |
| a | 1 | ids_ab | 2 | id | A | 2 | NULL | NULL | | BTREE | |
| a | 1 | idx_id | 1 | id | A | 2 | NULL | NULL | | BTREE | |
| a | 1 | idx_a | 1 | a | A | 2 | NULL | NULL | YES | BTREE | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
5 rows in set
mysql> explain
select * from a group by id order by a;
+----+-------------+-------+-------+---------------+--------+---------+------+------+-----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+--------+---------+------+------+-----------------+
| 1 | SIMPLE | a | index | NULL | ids_ab | 9 | NULL | 12 | Using temporary |
+----+-------------+-------+-------+---------------+--------+---------+------+------+-----------------+
1 row in set