CODEWITHSUNDEEP
mysqlsqlindexing
Martin AJ
Martin AJ

Reputation: 6697

My query needs what kind of index?

Here is my query:

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;

Well what index(es) query above needs? Either a single column or a multiple columns index? Here possible cases:

Ok which one is the best choose for that query?

Upvotes: 0

Views: 31

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269603

The best index for your query is on activate_account(user_id, date_time). This satisfies the where clause, with the column with the equality condition first and then the inequality.

You don't need a subquery. This should be fine:

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;

Upvotes: 2

Related Questions