Also, if a significant number of your records have the 'active' attribute assigned to 1, hte query optimizer will see it as more efficient to do a full table scan rather go through all the indirection layers the index imposes.
- michael On 9/13/07, Les Fletcher <[EMAIL PROTECTED]> wrote: > 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] > > -- - michael dykman - [EMAIL PROTECTED] - All models are wrong. Some models are useful. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
