How php displays data under each date; same as mysql statement optimization performance

Source: Internet
Author: User
@ Database Design idpay_namepay_time (timestamp int) @ how to arrange the table by time. The scheme of sorting data at the same time together @ is preferred groupby (pay_time) we can get all the time in each time, and loop all the data to compare whether the time and...

@ Database Design
Id pay_name pay_time (timestamp int)

@: Arrange the table by time, and arrange the data at the same time

@ The previous solution is
Select group by (pay_time) to get all the time
At each time, compare the time by repeating all the data to compare whether the time is equal to the above time, but compare all the time each time.
However, this is a waste of performance.

Reply content:

@ Database Design
Id pay_name pay_time (timestamp int)

@: Arrange the table by time, and arrange the data at the same time

@ The previous solution is
Select group by (pay_time) to get all the time
At each time, compare the time by repeating all the data to compare whether the time is equal to the above time, but compare all the time each time.
However, this is a waste of performance.

1. Add an index to pay_time first.

2. Retrieve the amount of data you need from the database by time. select * from table where pay_time> = t1 and pay_time <t2 order by pay_time desc limit pageSize;

3. Group your data by time at the front end.

SET SQL _mode = ''; -- if the group by statement is not added, an error is returned, which is not safe. We recommend that you add a varchar field storage time. After adding a field, you do not need to use the FROM_UNIXTIME () function select *, FROM_UNIXTIME (. pay_time, "% Y-% m-% d") as ptime FROM test a left join test B on FROM_UNIXTIME (. pay_time, "% Y-% m-% d") = FROM_UNIXTIME (B. pay_time, "% Y-% m-% d") and. id> = B. id group by. id,. pay_name, datas HAVING count (B. id) <= 3 order by. pay_time,. id
Count (B. id) <= 3 // here 3 is the number of entries in each time period

Then we can group the data based on 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.