Table columns:
one_id int(11),
two_id int(11),
ip_address int,
created_date datetime
The query:
Code: Select all
EXPLAIN SELECT count( * )
FROM table
WHERE one_id = '1'
AND created_date < now( )
GROUP BY date_format(created_date, '%m-%d-%y')
Moderator: General Moderators
Code: Select all
EXPLAIN SELECT count( * )
FROM table
WHERE one_id = '1'
AND created_date < now( )
GROUP BY date_format(created_date, '%m-%d-%y')
Code: Select all
CREATE INDEX one_id_date ON data ( one_id, created_date)
Now when I use the same query, with separate indexes on the one_id and created_date... This is what I getid select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE data range one_id_date one_id_date 14 NULL 369576 Using where; Using index; Using temporary; Using filesort
So the difference is the first shows Using index and is type range as opposed to ref, which I'm not sure what that means.. but even with both, it's using temporary and filesort... Isn't there a more efficient way?id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE date ref one_id,created_dtate one_id 5 const 329852 Using where; Using temporary; Using filesort