Re: Simple join taking longer than it should

[Rick James]

You have:

SELECT  COUNT(*)
    FROM  c
    JOIN  ccd on ccd.c_code = c.c_code
      and  ccd.data_id = 2
      and  ccd.`data` = "LOCALTEST"
    WHERE  (      c.c_entry_date between date_sub(now(), INTERVAL 1 hour)
                                    and  now()
              OR  c.c_entry_date between date_sub(now(), INTERVAL 169 hour)
                                    and  date_sub(now(), INTERVAL 168 hour)
              OR  c.c_entry_date between date_sub(now(), INTERVAL 337 hour)
                                    and  date_sub(now(), INTERVAL 336 hour) )

Depending on the version you are running, the "range" could be from INTERVAL 337 hour to NOW(), or it could be 3 short intervals. If the former, then changing to UNIONs will help significantly.

Depending on the frequency of data_id=2, it _may_ be beneficial to have an index starting with data_id. For that matter, INDEX(data_id, data(20)) might be very good.

The problem is a classic one -- you are filtering on two different tables. There is no efficient way to do such. Also, the optimizer is not great at picking which table to start with.

It may not matter, but your ranges have the "midnight bug" -- each range is 3601 seconds long.