Deleting rows faster than filtering rows

[aalnaif]

I am creating a temporary table which is simply a copy of two tables joined together, excluding rows which meet a certain criteria. For some reason, it is much faster for me to make a full copy of the joined tables, and then delete from the table the rows which meet the criteria, than it is to filter the rows in the first place.

drop table if exists active_customers;
create temporary table active_customers
     (select *
     from customers as c, customer_status as s
     where c.customer_ID = s.customer_ID);

delete from active_customers
where activity_status = 'I';

is much faster than

drop table if exists active_customers;
create temporary table active_customers
     (select *
     from customers as c, customer_status as s
     where c.customer_ID = s.customer_ID
     and s.activity_status = 'I');

In case it matters, I should note that customer_ID is indexed in both the customers and the customer_status tables, and activity_status is an indexed column in the customer_status table. Does anyone know how it is possible for the first query to be faster than the second query.

 

[feherke]

Hi

No idea, maybe in the second case does not use the index... But anyway I would try to [tt]join[/tt] with [tt]using[/tt]. In other databases [tt]using[/tt] performs better than [tt]on[/tt]; hopefully this stands for MySQL too.

[b]select[/b] *
[b]from[/b] customers
[b]inner join[/b] customer_status [b]as[/b] s [b]using[/b] (customer_ID)
[b]where[/b] s.activity_status = [i]'I'[/i]

Feherke.

http://rootshell.be/~feherke/