Problem LIMIT performance
Posted by:
Elja Trum
Date: May 05, 2005 05:07AM
Hi,
I'm migrating my website from MS Access to mySql.
I've a page that request the top 50 records of a table that contains over 85.000 records.
I used to have a query like this:
SELECT TOP 50 field1, field2, field3 FROM tabel1, tabel2, tabel3 WHERE (tabel1.fieldx = tabel2.fieldy) AND (tabel1.fieldx = tabel3.fieldz) AND (fieldq = 1) ORDER BY field1 DESC
Which works like a charm.
In mySql I believe this is to be replaced by:
SELECT field1, field2, field3 FROM tabel1, tabel2, tabel3 WHERE (tabel1.fieldx = tabel2.fieldy) AND (tabel1.fieldx = tabel3.fieldz) AND (fieldq = 1) ORDER BY field1 DESC LIMIT 50
However, this cause the mySql to give a time-out when I execute it.
The statement needs data from three different tables, the main table contains the most records (about 85.000), 'table2' contains 4.000 records and 'table3' contains about 60.000 records.
I can image it gives performance issues, but why didn't this problem occur on Access?
(And I really need the upgrade to mySql for performance with this many records. :))
Can anyone help me?
ps: The exact statement used is:
SELECT reaId, reaEntryId, reaName, reaDatum, reaTijd, reaMemo, usrUserID FROM tblReactie, tblUsers, tblDagboekEntry WHERE (tblReactie.reaName = tblUsers.usrNickname) AND (reaEntryId = dagEntryId) AND (dagPublic = 1) ORDER BY reaId DESC LIMIT 50
Elja