MySQL Forums
Forum List  »  Performance

How to use the index on main/sub-query
Posted by: Dean C.
Date: November 03, 2005 10:32AM

Hi all,

I have two tables (1GB each) with the same structure and an index on the field ID. I try to update one table, so it contains the same data as the second table while keeping time at a minimum.

The first thing I tried was:

INSERT INTO a
SELECT * FROM b
WHERE id NOT IN (SELECT id FROM a);

DELETE FROM a
WHERE id NOT IN (SELECT id FROM b);

I also tried using a temporary table first

CREATE TABLE temp
SELECT * FROM b
WHERE id NOT IN (SELECT id FROM a);

ALTER TABLE temp ADD INDEX (a);
INSERT INTO a
SELECT * FROM temp;

DELETE FROM a
WHERE id NOT IN (SELECT id FROM b);

Whatever I do, the first CREATE/INSERT runs about 50 minutes and the second DELETE 2 hours.

I used EXPLAIN and see that one id index is never used. I thought that MySQL should see that I have the necessary index on the id field and using it. But it uses the index on the subselect only and never on the main query.

Is there a way to force MySQL to use the id index on table a AND the id index on table b?

Or should I rewrite the whole query with left joins?

Oh, by the way. I'm using MyISAM as a storage engine, since I had better performance results in the past. Didn't try with INNODB yet.

Thank you so much!

Dean

Options: ReplyQuote


Subject
Views
Written By
Posted
How to use the index on main/sub-query
1772
November 03, 2005 10:32AM
1205
November 05, 2005 08:32AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.