MySQL limit paging generally check how many pages, limit how many will have performance problems?

General article paging is limit 20 data about, I do not know why, limit the biggest performance bottleneck where, the biggest value in where, a one-time fetch 5,000 have no problem, 10,000, how much suitable, good not, each page to take 20 too troublesome.

All along I had a problem, confused:

1: When to split the page,
2: When to have limit limits,
3: When you can get all the data directly and happily

Like hungry? Restaurant page, the restaurant may have a lot of categories, each category below there are many dishes, then this time it is directly to get all the data, there is no limit restrictions?

If the classification of a few, do not need to limit can be happy to take out all, but each category below the number of dishes may be a bit more (for example, I do not know how many) then take each of the following dishes need to use limit, if you want to limit so how many bar? If this is not possible there will be a "click to see more Food", then do not make an Ajax to take the rest of the data ah, take the rest of the data how to take it?

The concept is like this, but I am still very faint, I hope the great God combined with my example to point out the confusion, thank you!

Reply content:

General article paging is limit 20 data about, I do not know why, limit the biggest performance bottleneck where, the biggest value in where, a one-time fetch 5,000 have no problem, 10,000, how much suitable, good not, each page to take 20 too troublesome.

All along I had a problem, confused:

1: When to split the page,
2: When to have limit limits,
3: When you can get all the data directly and happily

Like hungry? Restaurant page, the restaurant may have a lot of categories, each category below there are many dishes, then this time it is directly to get all the data, there is no limit restrictions?

If the classification of a few, do not need to limit can be happy to take out all, but each category below the number of dishes may be a bit more (for example, I do not know how many) then take each of the following dishes need to use limit, if you want to limit so how many bar? If this is not possible there will be a "click to see more Food", then do not make an Ajax to take the rest of the data ah, take the rest of the data how to take it?

The concept is like this, but I am still very faint, I hope the great God combined with my example to point out the confusion, thank you!

1. When do you want to split pages?
   You should consider paging when the amount of data in the table is large

2. When do I have limit restrictions?
   Ditto

3. When can I get all the data directly and happily?
   Can fetch all data at a time when the amount of data in the table is small

Reading 20 data and reading 5,000 or more of the data, the performance is certainly 20 faster. And this fast, not only in the database, but also reflected in the transmission process on the network (the data volume is of course fast), the page rendering (the data volume less than the display page is faster). In addition, even if you show 5,000 data at a time, but the majority of users will be able to see twenty or thirty, and will not read all 5,000, so the extra data is wasted.

If it is a small element (such as text only, or a less informative element), the single page amount is set below 100. If it is a large element (large amount of information, with a type of text), the amount of a single page is best controlled below 30.

It's good enough. Take a lot of waste.
Users only see 20, you take 21 is redundant.
Return hundreds of thousand or even all of them, and that will be greatly affected.
First, the database query slow, take up more server resources, detect the results of slow transmission, occupy more bandwidth resources, followed by slow rendering, the browser will crash.

Do not think too much, this is only the product set down, usually at most 20~30, show how many are completely beautiful. Of course, when you say you can get all the data directly and happily. This situation is absolutely impossible to exist, how can get all the data, if the article volume reached the Tens how to do, it took too long to take out.

Page 20, and why not 5000,10000, the main or the first floor said, not to use, not to use , not so much

A database typically has a configuration that configures the maximum size of the data that you return per query request, and generally does not exceed that limit. As long as you do not exceed this limit, in fact, how to take is all right. However, the general use of on-demand principle, you do not need to take so much in principle, after all, take a lot, You for the network card bandwidth is occupied, I, for example, suppose you are thousands of M network card, limit 1W consumes bandwidth is 10K, then from the angle of network card bandwidth (not to mention the database performance problem) You can limit the QPS is 1000m/10k=10w QPS. Of course, if your network card can be tens of thousands of network cards. In addition, for the client, each request to obtain more data, in fact, also reduces the speed of the request to arrive.

Network Transmission consumption bandwidth
Network Transmission consumption bandwidth
Network Transmission consumption bandwidth
Important thing to say 3 times

