復制代碼 代碼如下:

select * from content order by id desc limit 0, 10

復制代碼 代碼如下:

select * from content order by id desc limit 10000, 10

復制代碼 代碼如下:

SELECT * FROM `content` WHERE id (SELECT id FROM `content` ORDER BY id desc LIMIT ".($page-1)*$pagesize.", 1) ORDER BY id desc LIMIT $pagesize

復制代碼 代碼如下:

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY content range PRIMARY PRIMARY 4 NULL 6264 Using where
2 SUBQUERY content index NULL PRIMARY 4 NULL 27085 Using index

復制代碼 代碼如下:

select * FROM `content` AS t1
JOIN (SELECT id FROM `content` ORDER BY id desc LIMIT ".($page-1)*$pagesize.", 1) AS t2
WHERE t1.id

復制代碼 代碼如下:

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY system NULL NULL NULL NULL 1
1 PRIMARY t1 range PRIMARY PRIMARY 4 NULL 6264 Using where
2 DERIVED content index NULL PRIMARY 4 NULL 27085 Using index

復制代碼 代碼如下:

select SQL_CALC_FOUND_ROWS * FROM tbl_name WHERE id > 100 LIMIT 10;
SELECT FOUND_ROWS();

復制代碼 代碼如下:

-- 注意下面語句中的條件 LIKE
SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name WHERE Name LIKE '%string%' id > 100 LIMIT 10;
SELECT FOUND_ROWS();

復制代碼 代碼如下:

-- 上面語句等價于下面語句，但性能方面應該提升非常非常的明顯：
SELECT COUNT(*) FROM tbl_name WHERE Name LIKE '%string%' ;
SELECT * FROM tbl_name WHERE Name LIKE '%string%' id > 100 LIMIT 10;