Slow MySQL Query

Forum Moderators: open

Message Too Old, No Replies

Slow MySQL Query

select over 3 mio rows

MrManager

1:04 pm on Apr 22, 2007 (gmt 0)

I'm running a SELECT query like the following on a MySQL 4 database. The table has around 3 mio rows, with a primary key on f_primaryKey (which is a varchar) and an index over (f_tinyint, f_integer).:


SELECT l.f_primaryKey, l.f_tinyint, UNIX_TIMESTAMP(l.f_datetime) dt
FROM table l
WHERE
(l.f_primaryKey NOT LIKE "%STR")
AND (
(f_tinyint = 1)
OR (f_tinyint = 2)
OR (f_tinyint = 3 AND f_integer < UNIX_TIMESTAMP() - 3600)
OR (f_tinyint = 4 AND (f_integer < UNIX_TIMESTAMP() - 3600 * 24 * 7))
)
ORDER BY (f_tinyint = 1) DESC, (f_integer!= 3) DESC, f_integer ASC
LIMIT 10000

This query takes around 5 - 10 minutes to complete, which is a bit too long for my taste. From looking at the MySQL process list, it seems like the server takes the most time sorting the result.

Does anybody know how I could optimize this further? Was making a varchar the primary key a mistake?

MrManager

6:32 pm on Apr 22, 2007 (gmt 0)

Never mind, I just split the query up into smaller parts.

Discovery

5:20 am on Apr 25, 2007 (gmt 0)

split the query up into smaller parts is not the solution.
1) While designing database scheme we can try to avoid primary key with varchar data type, its good to make primary key as numeric data type.
2) Do indexing on primary key and if required do on others key .
3)What i think your problem with
(l.f_primaryKey NOT LIKE "%STR")
because like always make query slow.

Slow MySQL Query

select over 3 mio rows

MrManager

MrManager

Discovery

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week