Shouldn't the first query have parenthesis? As in:
DELETE FROM test WHERE userID=XXXXX AND (testID<200000 OR
testID>800000);
Even if it works the other way, parenthesis make it more clear what
you're trying to accomplish.
-Angela
Michael Griffith wrote:
>
> 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
--
Angela Curtis
Innovative Business Consultants
http://www.ibc2001.com
---------------------------------------------------------------------
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
