While doing some benchmarks the other day, I saw surprisingly slow query results on columns that were indexed.
It's because MySQL won't use the Sex_Index index because the value that you are searching for (Namely "F" or "M") accounts for more than 30% of the data, probably 50%. So if "F" appeared in say 20% of the data, then the index would be used. MYSQL does this because the overhead in accessing the index for more than 30% of the data is greater than just accessing the data alone. It has to do at least 1 I/O to get the index entry, and another to get the corresponding data record.
From the manual: http://www.mysql.com/doc/en/MySQL_indexes.html
"Note that sometime MySQL will not use an index, even if one is available. One instance of this is when use of the index would require MySQL to access more than 30% of the rows in the table. (In this case a table scan is probably much faster, as it will require many fewer seeks.) However, if such a query uses LIMIT to only retrieve part of the rows, MySQL will use an index anyway, as it can much more quickly find the few rows to return in the result. "
You can try a "LIMIT 1000000" to force it to use an index. Of course for what you are trying to accomplish, a
select count(*) as Num from Sex group by Sex
would be much faster.
Mike
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
