我的查询语句如下:
SELECT tbl1.id FROM `tbl1` LEFT JOIN tbl2 ON tbl1.id = tbl.id WHERE tbl2.id = 5 ORDER BY tbl1.time DESC LIMIT 0 , 10我已为tbl1.time建立了索引 time_idx.
但是没有效果,explain命令后,还是看到Using temporory和Using filesort.
我用了强制索引命令如下还是不行
EXPLAIN SELECT tbl1.id FROM `tbl1` FORCE INDEX(time_idx) LEFT JOIN tbl2 ON tbl1.id = tbl.id WHERE tbl2.id = 5 ORDER BY tbl1.time DESC LIMIT 0 , 10我想知道像这种有JOIN的查询,如何用到相应的索引呢?
SELECT tbl1.id FROM `tbl1` LEFT JOIN tbl2 ON tbl1.id = tbl.id WHERE tbl2.id = 5 ORDER BY tbl1.time DESC LIMIT 0 , 10我已为tbl1.time建立了索引 time_idx.
但是没有效果,explain命令后,还是看到Using temporory和Using filesort.
我用了强制索引命令如下还是不行
EXPLAIN SELECT tbl1.id FROM `tbl1` FORCE INDEX(time_idx) LEFT JOIN tbl2 ON tbl1.id = tbl.id WHERE tbl2.id = 5 ORDER BY tbl1.time DESC LIMIT 0 , 10我想知道像这种有JOIN的查询,如何用到相应的索引呢?
SELECT tbl1.id FROM `tbl1` LEFT JOIN tbl2 ON tbl1.id = tbl.id WHERE tbl2.id = 5) a
ORDER BY tbl1.time DESC LIMIT 0 , 10
在ID上建立索引、time上建立索引试试
tb1.time建索引..试试.