|
You're hard-coding ten thousand discrete
values between the two IN clauses?? I'm not sure how MySQL processes a query
like that but I can imagine that the performance would not be good. Frankly, I
am:
a) suprised that you had the patience
to type in all of those values (and verify that you typed them
correctly)
b) amazed that MySQL actually executes a
statement that long without complaining that the statement is too
long
Are the discrete values really sequential
like the example you show? Because if they are you might find your life a lot
easier if you simply write your INs as BETWEENs. For example,
delete from modnaptrrecord_zone
where
modnaptrrecord_zone.modnaptrrecord_id between 593536 and 789123 and
modnaptrrecord_zone.modnaptrrecord_id not between 593536 and
789123
Even if there were a few ranges, the
query would still be a lot shorter if written with BETWEENs. For
example,
delete from modnaptrrecord_zone
where
(modnaptrrecord_zone.modnaptrrecord_id (between 100 and 500)
or
(modnaptrecord_zone.modnaptrrecord_id between 10000 and 11000))
and
(modnaptrrecord_zone.modnaptrrecord_id not (between 100 and 500)
or
(modnaptrecord_zone.modnaptrrecord_id between 10000 and 11000))
I've probably got at least one of
those conditions backward but you probably get the idea. It's a LOT easier to
write ranges than long lists of sequential values!
Another way that might improve
performance a lot is if you use a subqueries with your IN clauses. For
example:
delete from modnaptrrecord_zone
where
modnaptrrecord_zone.modnaptrrecord_id in
(select id from other_table where
customer_country = 'USA') and
modnaptrrecord_zone.modnaptrrecord_id
not in
(select id from other_table where
customer_country = 'CANADA')
In other words, if the values that
need to go into the IN list can be obtained by reading a table with a SELECT,
put the SELECT statement within the brackets instead of listing the hundreds of
discrete values. Of course, this assumes that you are using a version of MySQL
that supports subqueries!!
By the way, I'm assuming that the
lists needed for your two IN clauses are different from one another in some
way. Your query makes no sense if the two lists are the same. For example,
if your lists were the same - I'll just use a few values for each list to make
my point clear - you might have a query like this:
delete from modnaptrrecord_zone
where
modnaptrrecord_zone.modnaptrrecord_id in (1, 2, 3) and
modnaptrrecord_zone.modnaptrrecord_id
not in (1, 2, 3)
In other words, you want to delete
rows in table modnaprrecord_zone where the modnaptrrecord_id IS 1, 2 or 3 AND
where the mondaptrrecord_id IS NOT 1, 2, or 3 at the same time. That doesn't
make sense.
Now, if the values in the lists are
not in ranges and can't easily be obtained via SELECT queries, you may want to
consider modifying your tables to simplify this query. Let's say that those long
IN lists are just trying to specify customers who are in good standing with you,
i.e. customers who don't owe you any money and are prompt to pay their accounts.
You might find it a lot easier to add a column to your customer table that
indicates the customer's status and then simply search on the value of that
status flag. For example, let's say that a customer could have three statuses:
"Excellent", "Good" and "Poor". You could have a process that determines the
proper status for that customer; that might be a batch job that runs once a day
or once a week. Then, your query could simply search for the status like
this:
delete from modnaptrrecord_zone where
modnaptrrecord_zone.modnaptrrecord_id in
(select id from customer where status
= 'Poor') and
modnaptrrecord_zone.modnaptrrecord_id
not in
(select id from customer where status
= 'Good') --
Rhino
|
No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.394 / Virus Database: 268.9.0/368 - Release Date: 16/06/2006
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
