Welcome to WebmasterWorld Guest from 34.204.194.190

Forum Moderators: open

Message Too Old, No Replies

Slow MySQL Query

select over 3 mio rows

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

New User

10+ Year Member

joined:Mar 12, 2007
posts:26
votes: 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?

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

New User

10+ Year Member

joined:Mar 12, 2007
posts:26
votes: 0


Never mind, I just split the query up into smaller parts.
5:20 am on Apr 25, 2007 (gmt 0)

Junior Member

10+ Year Member

joined:Oct 4, 2006
posts:44
votes: 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.
 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members