我需要按出生日期起的年份分组。这就是我到目前为止所做的。如果您使用500000条记录运行存储过程,然后运行我编写的查询,您将看到它大约需要25秒。我该如何改进?
create table people(
id int not null auto_increment primary key,
`dob` date
);
delimiter //
drop procedure if exists date_random //
create procedure date_random(in low date,in upp date,in number int)
begin
declare i int default 0;
while i < number do
begin
insert into people (`dob`) values ( low + interval rand()* datediff(upp,low) day );
set i = i + 1;
end;
end while;
end //
delimiter ;
call date_random('1910-01-01',curdate(),500000);
delimiter //
create function `age`(dob date) returns int(11)
no sql
begin
return (year(curdate())-year(dob))-(right(curdate(),5)< right(dob,5) );
end //
delimiter ;
explain select sql_no_cache
concat_ws('-',min(age(dob)),max(age(dob))) as years,
count(*) as total
from people
group by if(age(dob)=0,1,ceil(age(dob)/5))
这是解释的输出
+----+-------------+--------+-------+---------------+------+---------+------+--------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+---------------+------+---------+------+--------+----------------------------------------------+
| 1 | SIMPLE | people | index | NULL | ip | 4 | NULL | 500000 | Using index; Using temporary; Using filesort |
+----+-------------+--------+-------+---------------+------+---------+------+--------+----------------------------------------------+
1 row in set (0.00 sec)
最佳答案
你的“年龄”功能可能更有效。与其强迫mysql将日期强制转换为字符串,不如执行子字符串、比较它们,然后转换为数字进行最后的减法运算,(year(now()) - year(dob)) - (dayofyear(now()) < dayofyear(dob))
-将其全部保留为数字,并至少消除一层转换。
另外,由于它使用了本机日期/时间函数,因此增加了MySQL在dob
列上使用索引的机会。您当前的方法无法处理索引,因为您是在查询时从日期字段动态派生文本值的。
关于mysql - mysql-按年份范围分组,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/4995627/