Edit Question - KnowledgeHub

Title Body

I'm a relative novice when it comes to databases. We are using MySQL and I'm currently trying to speed up a SQL statement that seems to take a while to run. I looked around on SO for a similar question but didn't find one. The goal is to remove all the rows in table A that have a matching id in table B. I'm currently doing the following: DELETE FROM a WHERE EXISTS (SELECT b.id FROM b WHERE b.id = a.id); There are approximately 100K rows in table a and about 22K rows in table b. The column 'id' is the PK for both tables. This statement takes about 3 minutes to run on my test box - Pentium D, XP SP3, 2GB ram, MySQL 5.0.67. This seems slow to me. Maybe it isn't, but I was hoping to speed things up. Is there a better/faster way to accomplish this? EDIT: Some additional information that might be helpful. Tables A and B have the same structure as I've done the following to create table B: CREATE TABLE b LIKE a; Table a (and thus table b) has a few indexes to help speed up queries that are made against it. Again, I'm a relative novice at DB work and still learning. I don't know how much of an effect, if any, this has on things. I assume that it does have an effect as the indexes have to be cleaned up too, right? I was also wondering if there were any other DB settings that might affect the speed. Also, I'm using INNO DB. Here is some additional info that might be helpful to you. Table A has a structure similar to this (I've sanitized this a bit): DROP TABLE IF EXISTS `frobozz`.`a`; CREATE TABLE `frobozz`.`a` ( `id` bigint(20) unsigned NOT NULL auto_increment, `fk_g` varchar(30) NOT NULL, `h` int(10) unsigned default NULL, `i` longtext, `j` bigint(20) NOT NULL, `k` bigint(20) default NULL, `l` varchar(45) NOT NULL, `m` int(10) unsigned default NULL, `n` varchar(20) default NULL, `o` bigint(20) NOT

Tags (comma-separated) Cancel