If I am not mistaken, group by only uses an index if the index is first
used as part of the where clause and is compared to a constant. You'd
need a two column index for this query:
INDEX(active,food)
The 'active' part would be used in the where clause, and the second part
( "food" ) could then be used by the group by.
alter table user add index(active, food);
This is off the top of my head, hope that it helps.
Les
James Tu wrote:
I have an index on `food` and on `active`, how come the result of the
EXPLAIN doesn't show the query using an index? I'm concerned that as
the query time will grow with the table.
My Query:
SELECT `food` , COUNT( `food` ) AS 'population'
FROM `users`
WHERE `active`=1
GROUP BY `food`
LIMIT 0 , 25
Result of Explain:
id select_type table type possible_keys key
key_len ref rows Extra
1 SIMPLE users ALL active_idx NULL NULL
NULL 11382 Using where; Using temporary; Using filesort
-James
--MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
