In table A, daily data (8 million rows), now A day, A table, A web page, can according to the conditions specified retrieval,
For example during retrieval 2020/02/10 ~ 2020/02/26 ID=5 state machine is turned off all the data (200), paging, and each page shows article 20,
The retrieval time for 30 seconds, how to improve the retrieval speed?

During tableList: according to remove all show table1_20200210 ~ table1_202002226
SELECT
YYYYY. Id AS "id", "
, YYYYY. The name AS "name"
, YYYYY. The status AS "status"
, YYYYY. MachineId AS "machineId
"The FROM (
SELECT
ZZZZZ. Id AS id
ZZZZZ. Name AS the name
, ZZZZZ. Status AS status
ZZZZZ. MachineId AS machineId
The FROM tableName AS ZZZZZ
WHERE ZZZZZ. Does=0
AND ZZZZZ. Status=2
AND ZZZZZ. MachineId=5
AND ZZZZZ. Start_time & gt;='2020-02-10 09:11:06. 0'
AND ZZZZZ. Start_time & lt; 'the 2020-02-26 10:11:06. 0'

) AS YYYYY
LEFT the JOIN tbl_Machine AS machine
ON YYYYY. MachineId=machine. Id
LEFT the JOIN tbl_team AS team
ON the machine. Area_id=team. Id
Limit 10

CodePudding user response:

 - 1. Create index for all data table, similar to the following 
The CREATE INDEX IX_tableName_start_time_machineId ON tableName (start_time, machineId) 
GO 
-- 2. Create a view, all related data table 
The CREATE VIEW view_all 
AS 
SELECT * FROM table1_20200210 
UNION ALL 
SELECT * FROM table1_20200211 
. 
GO 
-- 3. Create a SQL operations, zero update this view in the morning every day 

- 4. The front queries directly query view 

So, you can fast, write SQL also do not need so complicated