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
conditions:
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?