Select DATE_FORMAT(JoinDate,'%Y-%m-%d') as JoinDate,sum(user_info.IsActivate = 1) AS ActivedUser,count(0) AS TotalUser
from user_info where JoinDate like '2011-01%'
group by DATE_FORMAT(JoinDate,'%y-%m-%d') Order By JoinDate desc LIMIT 0,100我想根据JoinDate来Group By,但是JoinDate是带小时和分钟的,如果只是Group By JoinDate的话没有什么意义.
但是如果group by DATE_FORMAT(JoinDate,'%y-%m-%d')这样的话,查询的时候索引又无法用到.请高手指教.在线等候!!!!!!!!
mysql> show index from user_info;
+-----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation |Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| user_info | 0 | PRIMARY | 1 | ID | A | 30126 | NULL | NULL | | BTREE | |
| user_info | 0 | UserEmail | 1 | UserEmail | A | 30126 | NULL | NULL | | BTREE | |
| user_info | 0 | UserID | 1 | UserID | A | 30126 | NULL | NULL | | BTREE | |
| user_info | 1 | JoinDate | 1 | JoinDate | A | 30126 | NULL | NULL | YES | BTREE | |
+-----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
4 rows in set (0.02 sec)
t(0) AS TotalUser
-> from user_info where JoinDate like '2011-01%'
-> group by JoinDate Order By JoinDate desc LIMIT 0,100;
+----+-------------+-----------+-------+---------------+----------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+-------+---------------+----------+---------+------+------+-------------+
| 1 | SIMPLE | user_info | index | JoinDate | JoinDate | 9 | NULL | 100 | Using where |
+----+-------------+-----------+-------+---------------+----------+---------+------+------+-------------+
1 row in set, 1 warning (0.02 sec)
使用后的情况:mysql> explain Select DATE_FORMAT(JoinDate,'%Y-%m-%d') as JoinDate,sum(user_info
.IsActivate = 1) AS ActivedUser,count(0) AS TotalUser
-> from user_info where JoinDate like '2011-01%'
-> group by DATE_FORMAT(JoinDate,'%y-%m-%d') Order By JoinDate desc LIMIT 0
,100;
+----+-------------+-----------+------+---------------+------+---------+------+-------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref |rows | Extra |
+----+-------------+-----------+------+---------------+------+---------+------+-------+----------------------------------------------+
| 1 | SIMPLE | user_info | ALL | JoinDate | NULL | NULL | NULL |30126 | Using where; Using temporary; Using filesort |
+----+-------------+-----------+------+---------------+------+---------+------+-------+----------------------------------------------+
1 row in set, 1 warning (0.00 sec)
或则试下 IsAcJoinDate ,tivate 复合索引;
建立此字段索引
from user_info where JoinDate like '2011-01%'
group by JoinDate Order By JoinDate desc LIMIT 0,100这个看看.前面你已经把 DATE_FORMAT(JoinDate,'%Y-%m-%d') as JoinDate
group by 就不要再用date_format函数了.
2. group by DATE_FORMAT(JoinDate,'%y-%m-%d') 这种没有办法,MYSQL只能把所有的计算出来 再使用。这里最多是用group by date(JoinDate) 加快一点运算。
可以为其建一个冗余列,然后建索引,然后把like替换为between ......