My guess is that MySQL is trying to figure out if it would be faster to use the index or scan the whole table. MySQL is probably deciding that it is quicker to scan the whole table based on the low cardinality of the indices it can use. Thus, you've just added the query optimizer overhead to your query and accomplished no more than what was happening before.
To really take advantage of an index, you would need to create a compound index, one that spans multiple field. So you would have a single index that is made up of f01, f02, f03, ...
But if you are going to do that, you might as well just create a single field (char) that contains all your numbers. I don't know what your search criteria or data format is like, so that may not work. But it would considerably simplify your data structure, searches and code.
On May 6, 2005, at 4:14 PM, Jon Beyer wrote:
I was playing around with a table that had 100 tinyint fields. Each record contained other a '0' or a '1' for each field.
A query such as SELECT * FROM `foobar` WHERE `f01` =1 AND `f02` =1 AND `f03` =1 AND `f04` =1 AND `f05` =1 AND `f06` =1 AND `f07` =1 AND `f08` =1 AND `f09` =1 AND `f10` =1 runs in 0.07 seconds with 200,000 records.
When I add indeces to fields f01 and f02, the query now takes 0.23 seconds. Is this expected? Is this simply the time required to load the index? Are indeces simply not designed to work with something of such low cardinality?
Thanks, Jon
--
Chance favors only a prepared mind.
Jon Beyer 302N Dod Hall Princeton University Princeton, NJ 08544
609 986 8722
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
-- Brent Baisley Systems Architect Landover Associates, Inc. Search & Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
