Background, a mysql table, innodb, data of 30 million +, cooperate to index the speed of query data can also
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
Question: in the management of the page, do paging query, need to take according to the total amount, and then calculate the total number of pages,

Take the total amount according to select count (*) from table_name, time-consuming in 15 s, is there any way to optimize to within 0.5 s,

If not, consider do not calculate the total number of pages, only do previous and next page,

CodePudding user response:

30 million all of the data is not to see, paging is a must, as to the total number of see you how make the coun slow you can put into the session, or program initialization, or build a stored procedure, anyway, don't let the user to count the number;
Paging must to arrange the latest data on the first page

CodePudding user response:

The shortest field to the table, to be a index, and then count the fields give it a try

CodePudding user response:

Count (primary key)

CodePudding user response:

The

reference 3 floor AHUA1001 response:

count (primary key)

Is similar to the results, and even worse

CodePudding user response:

Use information_schema;

The select table_name, table_rows from tables
Where TABLE_SCHEMA='testdb'
The order by table_rows desc;

This is an estimate and does not guarantee the accuracy of the completely,