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