I have a query responsible for output of clients list that is regularly run by users.
The query is:
SELECT SQL_CALC_FOUND_ROWS p.lname, p.fname, p.patronymic, p.job,
p.post, p.zip, p.city, p.address, p.id, p.additional,
p.people_type_id, p.gender, p.permissions, p.user_id,
p.user_id, p.grading, p.progress, p.full_name, p.b1a,
p.b1b, p.b1c, p.b2a, p.b2b, p.b2c, p.b3a, p.b3b, p.b3c,
p.b4a, p.b4b, p.b4c, p.b5a, p.b5b, p.b5c, p.b6a, p.b6b,
p.b6c, p.b7a, p.b7b, p.b7c, p.b8a, p.b8b, p.b8c, p.b9a,
p.b9b, p.b9c, p.b10a, p.b10b, p.b10c, p.b11a, p.b11b,
p.b11c, p.b12a, p.b12b, p.b12c, p.b13a, p.b13b, p.b13c,
p.b14a, p.b14b, p.b14c, p.b15a, p.b15b, p.b15c, p.b16a,
p.b16b, p.b16c, p.b17a, p.b17b, p.count, p.finish_count,
p.partyId, t.description, a.description, p.contact_through,
DATE_FORMAT(p.next_call, '%d-%m-%Y') as next_call, p.recruiter,
p.b17c, p.l1a, p.l1b, p.l1c, p.l2a, p.l2b, p.l2c, p.l3a,
p.l3b, p.l3c, p.l4a, p.l4b, p.l4c, p.l5a, p.l5b, p.l5c,
p.l6a, p.l6b, p.l6c, p.l7a, p.l7b, p.l7c, p.l8a, p.l8b,
p.l8c, p.l9a, p.l9b, p.l9c, p.l10a, p.l10b, p.l10c, p.l11a,
p.l11b, p.l11c, p.l12a, p.l12b, p.l12c, p.l13a, p.l13b,
p.l13c, p.l14a, p.l14b, p.l14c, p.c1a, p.c1b, p.c1c, p.c2a,
p.c2b, p.c2c, p.c3a, p.c3b, p.c3c, p.c4a, p.c4b, p.c4c,
p.c5a, p.c5b, p.c5c, p.c6a, p.c6b, p.c6c, p.c7a, p.c7b,
p.c7c, p.c8a, p.c8b, p.c8c, p.c9a, p.c9b, p.c9c, p.c10a,
p.c10b, p.c10c, p.c11a, p.c11b, p.c11c, p.c12a, p.c12b,
p.c12c, p.c13a, p.c13b, p.c13c, p.c14a, p.c14b, p.c14c,
p.c15a, p.c15b, p.c15c, p.c16a, p.c16b, p.c16c, p.c17a,
p.c17b, p.c17c, p.c18a, p.c18b, p.c18c, p.c19a, p.c19b,
p.c19c, p.c20a, p.c20b, p.c20c, p.c21a, p.c21b, p.c21c
FROM people p
JOIN people_progress_id a ON p.progress = a.proc_level_id
JOIN people_grading_id t ON p.grading = t.grading_level_id
WHERE p.category=3
and p.status = 1
ORDER BY p.city desc
LIMIT 0, 50;
It regularly gets logged to mysql-slow-query log similarly to this:
# Time: 160718 17:18:32
# User@Host: server[server] @ localhost []
# Query_time: 4.098162 Lock_time: 0.000255 Rows_sent: 50 Rows_examined: 1508127
SET timestamp=1468851512;
SELECT SQL_CALC_FOUND_ROWS p.lname...
But when I run this in PHPMyAdmin the execution time is less than a second. Is there any reason why this happens? And how can I speed this up?
Add INDEX(category, status, city)
Rethink dumping that much data out -- do the users really need that much stuff?
Rethink how to get "calc found rows" -- either remove it from the UI, or look into ways of caching and/or approximating the value.
Rethink whether you really need t.description, a.description -- the JOINs are hurting performance (some).
(And, as already mentioned, the Query cache may be confusing the timings. Simply changing LIMIT 0,50 to LIMIT 0,51 will prevent the QC from kicking in. Of course, SQL_NO_CACHE is better.)