0

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?

user164863
  • 580
  • 1
  • 12
  • 29
  • 1
    Because the query gets cached. If you get result from the cache, then there's no much work involved and it gets "executed" pretty fast. This effect you're experiencing is not related to PHPMyAdmin, it just happens that you ran a query *then* used PHPMyAdmin to run the same query. However, 2nd time it gets executed - the result is pulled from cache, thus you concluded it's fast if you execute it via PHPMyAdmin. False positives are a b***h. – Mjh Jul 22 '16 at 13:14
  • 1
    This query appears to be doing a full table scan. What if some of the rows are locked some of the time? What if a whole lot of rows are locked? That's why this query appears to run fast sometimes but not at other times. – e4c5 Jul 22 '16 at 13:22
  • If you run `SELECT SQL_NO_CACHE .... (rest of your query) ` then you can verify whether it's the cache which affects the query speed. If you *still* get fast results, then @e4c5 is correct. Both scenarios are possible. – Mjh Jul 22 '16 at 13:32
  • yes, @Mjh is right. It could be either of these or both. – e4c5 Jul 22 '16 at 13:33
  • @Mjh Thank you, That's what I thought, but if that gets cached why this still comes to the slow-query-log? It doesn't get chached for all users or cache expires? Any way to always (or at least much longer) have that in cache sicne it seems to be the most slow query in the app? – user164863 Jul 22 '16 at 17:09
  • @e4c5 I understand the rows can be locked for update but can those be locked for SELECT too? – user164863 Jul 22 '16 at 17:11
  • When a row is being updated it cannot be read in a select – e4c5 Jul 22 '16 at 23:19

2 Answers2

1

  • 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.)

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • t.description , a.description that must have taken some spotting! +1 – e4c5 Jul 22 '16 at 23:24
  • They don't call me superfreak for nothing. – Rick James Jul 22 '16 at 23:28
  • Ok, I run now with SQL_NO_CACHE and I constantly get 3.4 seconds. If I take JOINs out I get 1 second less but still 2.4 seconds. Making INDEX(category, status, city) cut one more second down. But taking CALC_FOUND_ROWS produced the most effect and got it down to 0.003 seconds which is perfect. While I can duplicate data from other tables into one to avoid JOINs I would need to figure out something for this CALS_FOUND_ROWS which is used by JQuery datatable plugin. Thank you very much. – user164863 Jul 23 '16 at 18:56
  • 1
    Removing `CALC_FOUND_ROWS` is pretty much like removing the `LIMIT`. You may have to decide not to include "showing 50 out of 12345" in the UI. – Rick James Jul 23 '16 at 19:04
-2

You need to change the my.ini file to capture the slow queries. there are following parameters that you need to set up

  1. slow_query_log it should be ON
  2. log_slow_queries = 5; (That means MySql logs queries that takes more than 5 seconds)

Thanks, Rakesh

Rakesh
  • 1
  • the OP already has it setup, just asking why it's only sometimes slow – e4c5 Jul 22 '16 at 13:21
  • 1
    Downvote reason - you didn't bother to read the question so you provided something entirely different as answer. I suggest that you delete this. – Mjh Jul 22 '16 at 13:34