我的查询是指:
- 过滤人们不正确的日期,以便它只计算具有正确日期"日-月-年"(02-Dec-1986)的人
- 计算两个参数之间的年龄
我不知道1.029秒对于这种类型的查询是否正常,或者我正在做一些错误的事情,使它有点慢。如果我做了一些可以优化的事情,谁能给我一些建议?
,因为它代表我的查询取( 870,968 total, Query took 1.0297 sec)
实际的查询是
SELECT DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(STR_TO_DATE(m.m_dob, '%d-%b-%Y'))), '%Y')+0 AS age
FROM impressions AS i, mariners AS m
WHERE STR_TO_DATE(m.m_dob, '%d-%b-%Y') IS NOT NULL
AND m_dob != ''
AND DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(STR_TO_DATE(m.m_dob, '%d-%b-%Y'))), '%Y')+0 BETWEEN 13 AND 50
ORDER BY `age` DESC
根据您的评论和当前查询,您实际上并没有连接您的表。这两个表需要在:
上连接SELECT DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(STR_TO_DATE(m.m_dob, '%d-%b-%Y'))), '%Y')+0 AS age
FROM impressions AS i
INNER JOIN mariners AS m
ON i.SessionId = m.ID
WHERE i.impression =1
AND STR_TO_DATE(m.m_dob, '%d-%b-%Y') IS NOT NULL
AND m_dob != ''
AND DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(STR_TO_DATE(m.m_dob, '%d-%b-%Y'))), '%Y')+0 BETWEEN 13 AND 50
ORDER BY `age` DESC
当前查询返回日期的笛卡尔结果。