When using mySQL I've learned to avoid OR in any queries as much as possible
. Almost always this causes a major speed decrease. Consider this table:
CREATE TABLE test(
userID int, # (non-unique)
testID int, # (non-unique)
PRIMARY key(testid,userid)
);
Suppose this table is populated with 1,000,000 rows. Then do this:
DELETE FROM test WHERE userID=XXXX AND testID<200000 OR testID>800000;
This query is EXTREMELY slow becasue it looks at every row in the DB.
A significant improvement can be acheived by splitting it into 2 statements:
DELETE FROM test WHERE userID=XXXX AND testID<200000;
DELETE FROM test WHERE userID=XXXX AND testID>800000;
On real data I've acheived at least a ten fold increase doing this.
This is easy to optimize from the client side, however, I don't see any
reason why this optimization can't or shouldn't be build into the server.
Whenever an OR can be split into two separate queries on the same index this
optimization should work.
Food for thought.
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
