Dan Baker wrote:
"Eric Bergen" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
When you add that index are more than 30% of the rows in the table
DateTimeNext>1126215680?
There are currently 28.53% of the rows that have "DateTimeNext>1126215680"
Does this mean something of interest? If so, what?
Thanks
DanB
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
You may want to take a look at this page:
http://dev.mysql.com/doc/mysql/en/how-to-avoid-table-scan.html
Another possibility would be to change your data structures so that you
can use an equality, rather than a range scan. For example, make
DateTimeNext into a "date" or "datetime" field (rather than an int), and
then alter your SELECT statement to be
SELECT id,Name FROM tbl WHERE DateTimeNext = DATE(NOW());
Hope that helps,
Devananda vdv
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
