I am thinking on the problem, if there is a multiple columns on which search must work as fast as possible.
For example, see the table
TEST(COL_A, COL_B) with a (tree) index
TEST_IDX(COL_A, COL_B). This index can be used for the following
WHERE COL_A='x' AND COL_B='y'
WHERE COL_A='x' AND COL_B<'y'
But what to do, if both of the condition terms are using inequality? So I am thinking on a
WHERE COL_A<'x' AND COL_B<'y'? AFAIK, normal record-tree indices in such cases can’t work. I can imagine some data structure which could make such queries also fast, but I think it should use a much sophisticated data structure as a simple tree.
Do this in MySQL exist? Or in another SQL servers?
Create two separate indexes: idxA(COL_A) and idxB(COL_B).
MySQL will use both indexes and then merge the result.
Secion: 126.96.36.199.3 The Index Merge Sort-Union Access Algorithm