Force index (ix_type_status_dtime_cid_ptime)
WHERE ` type `=1 AND ` status `=1 AND ` dtime `=0 AND ` cid `=14
The ORDER BY ptime ` DESC LIMIT 150330,
Description:
Post millions of records in the table;
Filtered through the where clause condition, need to query the limit classification records 150000,
On the basis of this slow death do paging query, a query to more than 40 seconds, consult optimization method,
Thank you very much!
CodePudding user response:
With delay connection tryThe SELECT ` id `, ` ptime `, ` title `, ` area ` FROM ` post ` as a inner join
(SELECT ` id ` FROM ` post `
Force index (ix_type_status_dtime_cid_ptime)
WHERE ` type `=1 AND ` status `=1 AND ` dtime `=0 AND ` cid `=14
The ORDER BY ptime ` DESC LIMIT 150330, 10) as b
On Anderson, d=b.i d
I assume that id is the primary key here, if not actually, please change the primary key field