Delete sql query is very very slow

Question

I've a little problem; I have 2 tables: events and multimedia. events have the

id, 
device_id
created_at field

the primary key is the id and there's a index formed by device_id and created_at field.

multimedia table have the follower field:

id
device_id
created_at
data (this field is a blob field and contains a 20k string) 

the primary key is id and there's a index formed by device_id and created_by field.

The problem is when i want to delete the record with created_at before a data.

the query:

DELETE FROM events WHERE device_id = #{dev[0]} 
AND created_at <= '#{mm_critical_time.to_s}' 

is ok. In 5 or 6 second delete the record.

The query

DELETE FROM multimedia WHERE device_id = #{dev[0]} 
AND created_at <= '#{mm_critical_time.to_s}'

give me some problem, the execution start and never finish it.

what's the problem?

Answer 1

You probably need to create an index for the columns you are searching.

CREATE INDEX device_created_index
ON multimedia (device_id, created_at);

If you want to learn more about optimizing your queries, refer to the answer I gave here about using EXPLAIN SELECT: is there better way to do these mysql queries?

Answer 2

the order of the conditions is important, you havent't told us your database server but at least in Oracle it is, so try to reverse them like

DELETE FROM multimedia WHERE 
created_at <= '#{mm_critical_time.to_s}' 
AND device_id = #{dev[0]}  

or us an inner query on the fastest part

DELETE FROM multimedia WHERE 
created_at <= '#{mm_critical_time.to_s}' 
AND device_id in (select device_id from multimedia where device_id = #{dev[0]})

Also, i always break slow queries up and test the parts on speed so that you know where the bottleneck is. Some programs show you how long a query took and in Ruby you could use benchmark, you can supplement the delete with a select while testing.

so test:

select * FROM multimedia WHERE created_at <= '#{mm_critical_time.to_s}' 

and

select * from multimedia WHERE device_id = #{dev[0]}  

Success..

Answer 3

It is quite naive to give solutions to performance problems in relational databases without knowing the whole story, since there are many variables involved.

For the data you provided though, I would suggest you to drop the primary keys and indexes and run:

CREATE UNIQUE CLUSTERED INDEX uc ON events (device_id, created_at);
CREATE UNIQUE CLUSTERED INDEX uc ON multimedia (device_id, created_at);

If you really need to enforce the uniqueness of the id field, create one unique nonclustered index for this column on each table (but it will cause the delete command to consume more time):

CREATE UNIQUE INDEX ix_id ON events (id);
CREATE UNIQUE INDEX ix_id ON multimedia (id);