像这样的查询往往会耗费十几秒的时间
SELECT * FROM news WHERE cat_id = 4 ORDER BY id DESC LIMIT150000, 10
改进后
SELECT id, value, LENGTH(stuffing) AS len FROM t_limit ORDER BY id LIMIT 150000, 10
仍然需要数秒, 但是, 如果将ID作为子查询
SELECT l.id, value, LENGTH(stuffing) AS len
FROM ( SELECT id FROM t_limit ORDER BY id LIMIT 150000, 10) o
JOIN t_limit l ON l.id = o.id
ORDER BY l.id
这样查询在数十毫秒就可以完成. 具体的解释看 MySQL ORDER BY / LIMIT performance: late row lookups