"Devananda" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > 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());
I did notice that if I use an "=" comparison, that it will use the index. Unfortunately, I need all records that are "after a given date", and every record has a different date, so I can't use an "=" comparison. It does seem strange that the = will use the index, but a < or > won't. Thanks for the idea. DanB -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
