How PHP displays data under each date; Optimize performance with MySQL statements

Source: Internet
Author: User

@ Database Design
ID pay_name pay_time (timestamp int)

@ How to arrange the table by time, like a graph, with data at the same time

The scenario before @ is
Preferred group BY (Pay_time) get all the time
At each time, loop all the data to compare whether the time is equal to the above time, but compare all the times each time.
But this wastes performance.

Reply content:

@ Database Design
ID pay_name pay_time (timestamp int)

@ How to arrange the table by time, like a graph, with data at the same time

The scenario before @ is
Preferred group BY (Pay_time) get all the time
At each time, loop all the data to compare whether the time is equal to the above time, but compare all the times each time.
But this wastes performance.

1, the first to add index to pay_time.

2. Take the amount of data you need from the database in time, select * from table where Pay_time >=t1 and Pay_time < t2 ORDER BY pay_time desc limit Pagesiz E

3, in the front-end of your data grouped by time.

SET sql_mode = ''; --不加这一条语句group by 会报错 ,加了又不太安全。建议再加个varchar类型的字段保存时间,加了字段后就不用FROM_UNIXTIME()函数了select *,FROM_UNIXTIME(a.pay_time,"%Y-%m-%d") as ptime FROM test a LEFT JOIN test b on FROM_UNIXTIME(a.pay_time,"%Y-%m-%d") = FROM_UNIXTIME(b.pay_time,"%Y-%m-%d") and a.id >= b.id GROUP BY a.id,a.pay_name,datas  HAVING count(b.id) <= 3 ORDER BY a.pay_time,a.id
count(b.id) <= 3 //这里的 3 是每个时间段的条数

Then we'll just group the data according to Ptime.

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