To ascend a often use paging list interface response speed (the inside of the data come from a couple of millions of data table query, and temporarily not convenient to use redundant table implementation), query paging list data to the faster (using the index), but each time the query list for total number of records is very slow, consult everybody a great god have a better solution

CodePudding user response:

Query article number can slash those no associative table query conditions.
If it is slow, then reduce the associative table, splicing logic in the code, if the query condition to fall on the associative table, as far as possible into a check on the main table associated id, reduce fuzzy query, most of the fuzzy query.
Is just like you want to query the customer data, but there is a condition that fuzzy search, according to the project name needs to be associated with project list, but actually makes little sense to this condition, the project name of fuzzy have significance for business? Lenovo out specific project id, and then according to the project id of the customer, so your condition is that land on the customer table.

CodePudding user response:

Very fast, according to records of the query is paged to the total number of records query is very slow, according to the total number of records, the query condition is very limited and index optimization way, and must be used more table association (query condition)

CodePudding user response:

Federated query does not have to look-up table directly, you can use the stored procedure to hold a list of key data, such as you do is a statistical query, you only need to use each table in the column group by + count out into a temporary table, and then select from a couple of temporary list out the results you want, anyway, I do have a statistical figure 3 millions of data connection, is to do so

CodePudding user response:

refer to the original poster minhongxing response:

to raise a list often use paging interface response speed (the inside of the data come from a couple of millions of data table query, and temporarily not convenient to use redundant table implementation), query paging list data to the faster (using the index), but each time the query list for total number of records is very slow, consult everybody a great god have a better solution

Real-time demand is not high, the total number of records for caching, then brush the cache regularly,
Only when the paging operation limit, total number of records read cache will not run the count (*), twenty million data, paging about 100 milliseconds, kiss test!

CodePudding user response:

1, as the upstairs said, if the total number of records under the condition of real-time demand is not high do cache, then time refresh
2, if the total number of records required strong consistent, then the article need to statistics the number of conditions analysis analysis first, and then do a field for each condition, every time the database insert or delete records, number of records according to the condition changes, the situation is suitable for updates and inquire the circumstance of much less, if the update is very frequently is not suitable for,