Remember that the query is slow due to the order by DESC limit

Yesterday received a customer's question, the computer can open the website, on the phone does not open the 500 error. First login on the client's server to view the environment apache+mysql+php,php and MySQL occupy are relatively high, according to experience that is the problem of MySQL, landing MySQL with show processlist to view the process, Find a query that has been sending date

mysql> show processlist;+------+------+-----------------+--------+---------+------+-------------- +------------------------------------------------------------------------------------------------------+|  id   | user | host             | db     | Command | Time | State         | Info                 |+------+------+-----------------+--------+---------+------+-------- ------+------------------------------------------------------------------------------------------------------+|  1832 | root | localhost:53490 | NULL   | Query    |    0 | null         |  showprocesslist                | |  1842 | root | localhost:53508 | yungou | Query    |    4 | sending data | select a.id,a.q_user,a.q_showtime, A.THUMB,A.TITLE,A.Q_UID,QISHU,ANNOUNCED_TYPE,Q_END_TIME&NBSP, (select  '  |+------+------+------------ -----+--------+---------+------+--------------+---------------------------------------------------------------- --------------------------------------+2 rows in set  (0.00 SEC)

Obviously wrong, it's too slow to take this statement out alone. 48s.

Explain analyze this statement:

Mysql> explain select a.id,a.q_user,a.q_showtime,a.thumb,a.title,a.q_uid,qishu,announced_ type,q_end_time , (select  ' time '  FROM  ' Go_member_go_record '  where shopid =  a.id ORDER BY  ' time '  DESC LIMIT 1 )  as gm_time from   ' go_shoplist '  as a where ' Shenyurenshu '  <=0 ORDER BY  ' gm_time '  desc limit 4\g;*************************** 1. row ***************************            id: 1  select_type:  primary        table: a          type: rangepossible_keys: shenyurenshu           key: shenyurenshu      key_len: 4           ref: null         rows: 945         extra: using where; using filesort***************************  2. row ***************************            id: 2  select_type: DEPENDENT SUBQUERY         table: go_member_go_record         type:  Indexpossible_keys: shopid          key: time       key_len: 63           ref: NULL         rows: 1         Extra: Using where2 rows in set  (0.00&NBSP;SEC)

A look at the type is range on the tragedy, slow is affirmative, but 945 records are not so slow ah!!

There is no way to use the simplification method to locate the error, first remove the subquery, directly

Select A.id,a.q_user,a.q_showtime,a.thumb,a.title,a.q_uid,qishu,announced_type,q_end_time from ' Go_shoplist ' as a where ' Shenyurenshu ' <=0 ORDER by ' gm_time ' DESC LIMIT 4;

No problem, the speed of the leverage ~ ~

That's the problem with subqueries. Oh, where do you move the knife? ORDER BY DESC limit

First remove the sorting, query the leverage of ~ ~

Then think of the order by DESC limit 1 How to replace it, the meaning is the query maximum value, then I directly with Max can? Edit now

Select A.id,a.q_user,a.q_showtime,a.thumb,a.title,a.q_uid,qishu,announced_type,q_end_time, (select MAX (' time ') From ' Go_member_go_record ' where shopid = a.id) as Gm_time from ' go_shoplist ' as a WHERE ' Shenyurenshu ' <=0 ORDER by ' Gm_time ' DESC LIMIT 4;

Open the website, haha, speed leverage ~ ~

This article from the "Technology" blog, declined to reprint!

Remember that the query is slow due to the order by DESC limit