MySQL statement on a school system I have created some years back was working fine but now takes close to 30 seconds to pull what appears to me a simple statement but cannot figure out how to improve it. I am wondering if anyone can help me re-write this statement for a faster response. The statement is:
SELECT es.*,c.mainsubarea AS subject, b.name,b.email,GROUP_CONCAT(doc.document_file SEPARATOR "|") document_file
FROM usersubinfo es
LEFT JOIN userinfo b ON (es.uid=b.uid)
LEFT JOIN lkptsubjectarea c ON (es.mainsubjectarea=c.id)
LEFT JOIN lkptdeliverytime d ON (es.deliverytime = d.id)
LEFT JOIN documents doc ON (es.id = doc.order_id)
WHERE es.id AND es.is_active='Yes'
GROUP BY es.id
ORDER BY es.joindate
DESC LIMIT 0,25
2
Answers
First and foremost, consider writing a valid ANSI SQL aggregate query with changes to your
GROUP BY
andSELECT
clauses. Currently, your query includes only one column inGROUP BY
but all columns fromusersubinfo
table withSELECT es.*
in addition to other non-aggregated columns. You even order by a column not inGROUP BY
.Such a query runs against the SQL standard and will fail in most RDBMS’s but allowable in MySQL due to its
ONLY_FULL_GROUP_BY
mode turned off which dangerously allows:Since you have an aggregate function,
GROUP_CONCAT
, all other non-aggregated columns should be placed inGROUP BY
clause. If you need to add columns inSELECT
add it also toGROUP BY
. Also, you may have a redundantLEFT JOIN
which serves no purpose for otherJOIN
s or columns forSELECT
.Additionally, by avoiding
SELECT *
you avoid bringing in unneeded even newer columns, allowing indexes to run effectively over large table scans, and avoid sending large amount of content over network. See Why is SELECT * considered harmful?Add Indexes: These may help:
Remove LEFT: Is there a reason for
LEFT JOIN
instead ofJOIN
? I think not. See if you get the same results withoutLEFTs
.Remove bogus test: Why
WHERE es.id
? Ifid
is thePRIMARY KEY
ofes
, that test will always be true.Improve GROUP+ORDER: Change
–>
That avoids two passes over the data — one for the GROUPing, and another for the ORDERing. Meanwhile, I assume that my grouping and ordering is "just as good".
Turn inside-out: That brings up another issue, one I call "explode-implode". That’s where you Join together a lot of rows, only to get rid of most of them. So…
Start by finding the 25 ids desired with as little effort as possible:
And include that as a ‘derived’ table in the rest:
This is likely to be faster because the tables other than
usersubinfo
will be touched only 25 times.(I think this will happen to avoid the "only_full_group_by" issue to which Parfait refers.)