Geek asks and answers

Optimizing this mysql query to use my indexes if possible?

I have a mysql query that I thought should use my indexes, but still it seems like you need to scan a lot of rows (I think).

Here is my request:

SELECT DISTINCT DAY(broadcast_at) AS 'days' 
from v3211062009 
where month(broadcast_at) = 5 and 
year(broadcast_at) = 2012 
and deviceid = 337 order by days;

My table has an index setting on broadcast_at, deviceid . However, the results of the explanation for this query are as follows:

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  v3211062009 ref indx_deviceid,indx_tracking_query   indx_tracking_query 4   const   **172958**  Using where; Using index; Using temporary; Using filesort

I do not understand why he should look for so many lines. The total number of rows for this deviceid entry is 184085 , so my query seems to almost examine them all to get a result set. Does the index work in broadcast_at?

, , - , . .

+3
1

, MySQL broadcast_at, .

, :

SELECT DISTINCT DAY(broadcast_at) AS 'days' 
from v3211062009 
where broadcast_at >= ('2012-05-01') AND
      broadcast_at < ('2012-06-01') 
and deviceid = 337 order by days;
+4

More articles:


All Articles