0

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?

Improve this question
1
  • Spatial indexes (R-trees and other types) can be used for such queries. MySQL has R-trees (but can be added for MyISAM tables only.) Postgres has also some other types of indexes that can useful. You are right that simple b-trees will not be very efficient.
    – ypercubeᵀᴹ
    May 28 '14 at 23:19
2

Create two separate indexes: idxA(COL_A) and idxB(COL_B). MySQL will use both indexes and then merge the result.

http://dev.mysql.com/doc/refman/5.0/en/index-merge-optimization.html

Secion: 8.3.1.4.3 The Index Merge Sort-Union Access Algorithm

Improve this answer
2
  • 1
    Good but it won't be very efficient.
    – ypercubeᵀᴹ
    May 28 '14 at 23:18
  • @ypercubeᵀᴹ Yes, this is a very ineffective solution, but this is the most what mysql can offer. :-( Fortunately I managed to avoid to use MySQL in the latest years.
    – peterh
    Mar 30 '19 at 14:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.