Re: Optimizing query WHERE date0
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 DateTimeNext1126215680? There are currently 28.53% of the rows that have DateTimeNext1126215680 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 DateTimeNext1126215680 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]
Optimizing query WHERE date0
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 DateTimeNext1126215680 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]
Re: Optimizing query WHERE date0
When you add that index are more than 30% of the rows in the table DateTimeNext1126215680? 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 DateTimeNext1126215680 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]
Re: Optimizing query WHERE date0
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 DateTimeNext1126215680? There are currently 28.53% of the rows that have DateTimeNext1126215680 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 DateTimeNext1126215680 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]
Re: Optimizing query WHERE date0
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 DateTimeNext1126215680? There are currently 28.53% of the rows that have DateTimeNext1126215680 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 DateTimeNext1126215680 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]
