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

Source: Internet
Author: User
Tags php and mysql

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.