When you add that index are more than 30% of the rows in the table
DateTimeNext>1126215680?
Dan Baker wrote:
I have lots of tables that are similar in nature:
id int(11) PRI NULL auto_increment
Name varchar(30)
DateTimeNext int(11)
The "DateTimeNext" field represents when this records needs attention. A
value of zero indicates it is being ignored. There are times when *lots* of
records DateTimeNext values will be zero.
I want to find all records in the database that need attention today, so a
typical query looks like:
SELECT id,Name FROM tbl WHERE DateTimeNext>1126215680
When I EXPLAIN this query, I get the following:
table type possible_keys key key_len ref rows Extra
Site, ALL, NULL,NULL, NULL, NULL, 53587,Using where
If I add an index for "DateTimeNext", the EXPLAIN shows:
table type possible_keys key key_len ref rows Extra
Site,ALL,DateTimeNext,NULL,NULL,NULL,53587,Using where
It appears that the index does NO good in this query.
Is there anything I can do to optimize this query? Alter the table to
improve the query? Do anything to not scan the entire stinkin' table?
Thank you,
DanB
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
