desc SELECT * FROM test FORCE(idx_m_n) WHERE m=1 ORDER BY n LIMIT 1000,10;贴出结果看看
explain desc SELECT * FROM test FORCE(idx_m_n) WHERE m=1 ORDER BY n LIMIT 1000,10;
贴记录出来看看 SELECT a.* FROM test A INNER JOIN (SELECT ID FROM test WHERE m=1 ORDER BY n LIMIT 1000,10) B ON A.ID=B.ID
把索引改为: idx_m_n(m,n,ID)后然后写优化语句 ???
有人给我的答案是:索引改为: idx_m_n(m,n,ID); SELECT ID FROM test FORCE(idx_m_n) WHERE m=1 ORDER BY n LIMIT 1000,10; SELECT * FROM test WHERE ID IN (ids); 可是本人还是不明白: 1 为什么要分开写? 2 第二个SQL语句中 ID IN(ids), ids是什么意思?
与我的SQL语句基本一致 idx_m_n(m,n,ID); SELECT a.* FROM test a inner join (SELECT ID FROM test FORCE(idx_m_n) WHERE m=1 ORDER BY n LIMIT 1000,10 ) b on a.id=b.id
版主能帮忙回答下: 索引改为: idx_m_n(m,n,ID); SELECT ID FROM test FORCE(idx_m_n) WHERE m=1 ORDER BY n LIMIT 1000,10; SELECT * FROM test WHERE ID IN (ids); 1 为什么要分开写? 2 第二个SQL语句中 ID IN(ids), ids是什么意思?谢谢!
不能,因为不是我给出的, 用不着2条SQL语句,用1条就OK了,你自己运行 SELECT ID FROM test FORCE(idx_m_n) WHERE m=1 ORDER BY n LIMIT 1000,10 看看速度、执行计划就知道了
select * from test a, (select ID from test where m=1 ORDER BY n LIMIT 1000,10) b where a.id =b.id order by n
SELECT a.* FROM test A
INNER JOIN
(SELECT ID FROM test WHERE m=1 ORDER BY n LIMIT 1000,10) B
ON A.ID=B.ID
把索引改为: idx_m_n(m,n,ID)后然后写优化语句 ???
SELECT ID FROM test FORCE(idx_m_n) WHERE m=1 ORDER BY n LIMIT 1000,10;
SELECT * FROM test WHERE ID IN (ids); 可是本人还是不明白:
1 为什么要分开写?
2 第二个SQL语句中 ID IN(ids), ids是什么意思?
idx_m_n(m,n,ID);
SELECT a.* FROM test a
inner join
(SELECT ID FROM test FORCE(idx_m_n) WHERE m=1 ORDER BY n LIMIT 1000,10 ) b
on a.id=b.id
版主能帮忙回答下:
索引改为: idx_m_n(m,n,ID);
SELECT ID FROM test FORCE(idx_m_n) WHERE m=1 ORDER BY n LIMIT 1000,10;
SELECT * FROM test WHERE ID IN (ids); 1 为什么要分开写?
2 第二个SQL语句中 ID IN(ids), ids是什么意思?谢谢!
用不着2条SQL语句,用1条就OK了,你自己运行
SELECT ID FROM test FORCE(idx_m_n) WHERE m=1 ORDER BY n LIMIT 1000,10
看看速度、执行计划就知道了
from test a, (select ID from test where m=1 ORDER BY n LIMIT 1000,10) b
where a.id =b.id
order by n