On Thu, 2004-02-19 at 08:24, Alex Greg wrote:As BETWEEN is equivalent to the AND form anyway, I think the performance difference could be simply due to the smaller amount of parsing involved and simpler optimiser path. If you think about it, and AND statement can join predicates about different columns that could be in different tables (or even databases). The BETWEEN form says that both constraints are on the same column, and says so in a manner that's easier to deal with, for example:
I have a select query which is selecting all records in a table (which has
around 8,000,000 rows in). "time" is a field of type "time". Should I be using
<= and >= or BETWEEN to find records in a certain range? Which does MySQL
optimise for?
I can not speak to specifics in the code, but I have 150 million row InnoDB table that has an int field that stores a unix_timestamp() and I've gotten slightly better performance using BETWEEN rather than AND, but this is largely anecdotal, as I don't have hard numbers. Your best bet would be to test performance yourself. Be sure you are using an index on that column in your query (verify that with explain) -- using an index will make the most difference, of course.
SELECT * FROM table WHERE col1 >= 25 AND col2 < 38 AND col1 <= 1000;
Just the fact that the end-points on your col1 index are decied at two different points of the query will have some small impact on performance.
I'm betting that prepared statement and stored procedure versions would have basically identical performance regardless of the form used though.
Regards,
Chris
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
