-3

I am recently studying about database indexing in MySQL. I read some articles and found a great web page: http://mysql.rjweb.org/doc.php/index_cookbook_mysql

I read in this page and some others that we can have just one inequality condition in order to use indexing performance and we should use it as last column in composite indexes. I can't understand why just one? What is the reason that limit us for using more than one inequality constraints in set of columns in composite index?

Another useful link that I studied: https://www.freecodecamp.org/news/database-indexing-at-a-glance-bb50809d48bd/

  • 3
    That's not a mysql problem. It's a limitation of b-tree indexes. Some DBMS have other types of indexes (R-tree, GIN, GIST, ...) that are better for different search conditions. – ypercubeᵀᴹ Sep 28 at 15:37
  • @ypercubeᵀᴹ Thanks for your answer. Can you explain more? How b-tree structure apply this limitation? Can you introduce me some references? – Reza Hedayati Sep 28 at 16:26
2

This is not specific to MySQL, it is about B-tree indexes in general.

Leaving aside the implementation details, you can imagine a B-tree index as a sorted list of the indexed columns with a pointer to the table.

So if you imagine a two-column index on (num1, num2), it would look somewhat like this:

  num1  |  num2  | pointer
--------+--------+---------
      1 |      1 |
      1 |      3 |
      1 |     42 |
      1 |     42 |
      1 |  28643 |
      1 |  36001 |
      2 |      1 |
      2 |     41 |
      2 |   1001 |
      2 |  10000 |
      5 |      2 |
      5 |    123 |
      5 |   2054 |
...

Now an index can be used efficiently if all the columns you are looking for are right next to each other in the index. (There is what MySQL calls a loose index scan with which an index can be used differently, but that does not apply to the discussion at hand.)

If your query contains

WHERE num1 = 2 AND num2 > 10

it is obvious that the index can be used efficiently: all these index entries are right next to each other.

If your query contains

WHERE num1 <= 5 AND num2 > 1000

then the index can be used for the first condition, because all these rows are right next to each other), but among these rows, the ones with num2 > 1000 are all over the place, so this second condition can only be used as a filter to remove rows found by the index scan.

  • Great! Thanks for your answer. – Reza Hedayati Sep 30 at 9:03

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