ORDER BY on 2M rows kills performance permanently

[p1zza]

i've got a table with ~2 million rows. i have a select that pulls out an int, a varchar(200) and a CHAR(8) by calculating which entry is the most out-of-date, based on an auto-updating timestamp field and a tinyint priority field.

the query is:

SELECT url_id, url, hash
FROM url
WHERE priority > 0
ORDER BY (ADDDATE(updated_date, INTERVAL (priority * 5) HOUR) - CURRENT_TIMESTAMP()) ASC, url_id ASC
LIMIT 1000;

now, i understand that the ORDER BY is expensive since it involves date calculations against 2 million rows... i expect the query to take awhile... it takes about 30 seconds on average. the thing is, is that if i comment out that one ORDER BY clause, the database server performs significantly better forever until the mysql server is rebooted. i would expect perhaps mysql to take a while to "recover" from the expensive query, but it never does, it just crawls along.

if i leave the ORDER BY in my app it is able to process about 1 record per second (each processing of a record could conceivably involve about 5 significant but not unusual selects, also perhaps 10-100 inserts and no deletes from the same table). leaving everything exactly the same, without the ORDER BY the app can process about 3-4 records per second. the performance improvement is very noticeable, even just looking at the stdout of the app.

i assume mysql is eating up all my memory and swapping which is slowing everything to a crawl... how can i keep mysql from doing this? i've already a conservative my.cnf, but to no avail. thanks in advance.

 

[Bastien]

Try using the EXPLAIN PLAN to see how the engine is working the query. It may be that adding an index if its not doing a full table scan may speed it up or you may find that you need to disable all the indexes to help speed up the query.



Bastien

Any one have a techie job in Toronto, I need to work...being laid off sucks!