optomise very large table query

Question

I have a table that is close to 20million records and growing. The table was setup as innodb. There is a primary index on the two main fields:

`entries_to_fields`
entry_id    int(11) NO  PRI NULL     
field_id    int(11) NO  PRI NULL     
value   text    NO      NULL

Despite the number of records, most of the queries to this table are exceptionally quick, except for the following:

DELETE FROM `entries_to_fields` WHERE `entry_id` IN (SELECT `id` FROM `entries` WHERE `form_id` = 196)

This deletes all entry data for a specific form.

Currently this is taking over 45 seconds, even if the entries table returns no results.

My question is can is there a simple change to the entries_to_fields structure I can make, or can I optomise my query further.

score 3 · Accepted Answer · answered Aug 02 '11 at 06:23

3

After I read your answer, I wrote this query that may help you as well (in future).

DELETE entries_to_fields
FROM entries_to_fields
JOIN entries
ON entries_to_fields.entry_id = entries.id
WHERE entries.form_id = 196

... entries.form_id field should be indexed.

answered Aug 02 '11 at 06:23

Devart

Thanks! I wasn't aware that you could have inner joins within deletes.. Learn something new every day! – Ben Rowe Aug 02 '11 at 06:35

score 1 · Answer 2 · answered Aug 02 '11 at 06:15

After a bit of trial & error + googling, I found using IN on index fields on large tables is a very bad practice.

I've broken the sub-query into a separate query and then created a dynamic query as follows:

DELETE FROM `entries_to_fields` WHERE `entry_id` = 232 OR `entry_id` = 342 ...

Despite generating a potential large query, this executes within ~1sec now. Even when deleting 1000's of entries.

score 1 · Answer 3 · answered Aug 02 '11 at 06:27

1

I would look at the query plan, my guess is the subquery is returning NULL and making the delete full scan.

see :

answered Aug 02 '11 at 06:27

Kevin Burton

3 Answers3