I have an index m_idx on (k1,k2,k3)
If I do
SELECT c1,c2,c3... FROM tb FORCE INDEX (m_idx) WHERE k1=500 AND k2 IN(...) AND k2>2000 ORDER BY k1 LIMIT 1000;
OR
SELECT c1,c2,c3... FROM tb FORCE INDEX (m_idx) WHERE k1 IN (500,1000,1500 ...) AND k2 IN(...) AND k2>2000 ORDER BY k1 LIMIT 1000;
Handler_read_next = 999
BUT if I try to use a range on k1:
SELECT c1,c2,c3... FROM tb FORCE INDEX (m_idx) WHERE k1>=500 AND k2 IN(...) AND k2>2000 ORDER BY k1 LIMIT 1000;
Handler_read_next = 58035
In all cases EXPLAIN says that the key used is m_idx
But I think that in the third case m_idx it s not used (I also have an index only on k1).
Otherwise I don t understand why it is reading more than 1000 rows.
I was expecting to scan m_idx index, and ONLY the first 1000 rows that meet the conditions to be read from table.
But in fact I think that for the third case it scans the index and those rows who meet k1 condition are read from the tb and k2 and k3 conditions are checked after the rows are read from tb.
I use: MySql with MyISAM, WINDOWS 7 64, the tb has 1 mil rows;
So my questions are:
Is it possible to select by range on leftmost multiple-index?
OR
I m doing something else wrong?
Thank you.