Table articles:
id|category_id|title|created|updated
Index:
ADD KEY `category_id` (`category_id`,`created`)
My testing:
SELECT sql_no_cache * FROM `articles` WHERE category_id=1 order by created limit 0,15
Average time: 0.0003
SELECT sql_no_cache * FROM `articles` WHERE category_id=1 order by updated limit 0,15
Average time: 0.0019
SELECT sql_no_cache * FROM `articles` WHERE category_id in (1,2,3) order by created limit 0,15
Average time: 0.0018
SELECT sql_no_cache * FROM `articles` WHERE category_id in (1,2,3) order by updated limit 0,15
Average time: 0.0018
created 做了二级索引, query1 明显用了索引(对比 query2 ),但是 query3 没用索引。请问如何索引提高 query3 的查询效率?
1
realpg 2016-03-03 21:13:14 +08:00
category_id create 联合索引
category_id updated 联合索引 注意顺序, category 在前 |