查詢條件中無主鍵的分頁查詢sql優化
- 主要優化點:
- 分頁優化:查詢條件中無主鍵,無法通過
或where {#id} > {startRow}
優化。如有其他優化方案麻煩告知,非常感謝!where {#id} between {startRow} and {endRow}
- where添加優化:通過調整條件位置順序,并以此位置順序建立索引解決
-
-
SELECT {queryColumns} FROM {tabelName} WHERE {queryCondition} ORDER BY {orderByCondition} ASC limit ?,?
-
SELECT {queryColumns} FROM {tabelName} inner join (SELECT {id} FROM {tabelName} WHERE {queryCondition} ORDER BY {orderByCondition} ASC limit ?,?) as t using({id})
-
- 分頁優化:查詢條件中無主鍵,無法通過