这是我的查询:
SELECT 1
FROM ( SELECT count(*) AS num_week,
ifnull(sum(date_time > unix_timestamp(DATE_SUB(now(), INTERVAL 1 day))),0) as num_day,
ifnull(sum(date_time > unix_timestamp(DATE_SUB(now(), INTERVAL 1 hour))),0) as num_hour,
ifnull(sum(date_time > unix_timestamp(DATE_SUB(now(), INTERVAL 1 minute))),0) as num_1min
FROM activate_account
WHERE user_id = ?
AND date_time > unix_timestamp(DATE_SUB(now(), INTERVAL 1 WEEK))
) a
WHERE num_week < 12 AND num_day < 6 AND num_hour < 4 AND num_1min < 1;
那么上面的索引查询需要什么?单列索引还是多列索引?以下是可能的情况:
-
activate_account(user_id, date_time)
-
activate_account(date_time, user_id)
-
activate_account(date_time)
-
activate_account(user_id)
好的,哪一个是该查询的最佳选择?
查询的最佳索引在 activate_account(user_id, date_time)
上。 这满足where
子句,首先是具有相等条件的列,然后是不等式。
您不需要子查询。 这应该没问题:
SELECT count(*) AS num_week,
COALESCE(sum(date_time > unix_timestamp(DATE_SUB(now(), INTERVAL 1 day))),0) as num_day,
COALESCE(sum(date_time > unix_timestamp(DATE_SUB(now(), INTERVAL 1 hour))),0) as num_hour,
COALESCE(sum(date_time > unix_timestamp(DATE_SUB(now(), INTERVAL 1 minute))),0) as num_1min
FROM activate_account
WHERE user_id = ? AND
date_time > unix_timestamp(DATE_SUB(now(), INTERVAL 1 WEEK))
HAVING num_week < 12 AND num_day < 6 AND num_hour < 4 AND num_1min < 1;