Форум пользователей MySQL

Просматривая блог перконы наткнулся на

https://www.percona.com/blog/2015/04/27/indexing-101-optimizing-mysql-queries-on-a-single-table/ написал:

Multiple inequalities

SELECT * FROM t WHERE c > 100 and b < 10 and d = 'xyz'

   
As we have 2 inequalities, we already know that we will not be able to filter on both conditions (*). So we have to make a decision: will we filter on (d, b) or on (d, c)?

It is not possible to tell which option is better without looking at the data: simply choose the column where the inequality is the most selective. The main point is that you must put the column(s) with an equality first.

(*) Actually there is a way to ‘filter’ on both inequalites: partition on b and add an index on (d, c) or partition on c and add an index on (d, b). The details are out of the scope of this post but it might be an option for some situations.

Мысль очевидна, но никогда не смотрел на партиции как способ оптимизации запросов с двумя неравенствами. Вот что значит косность мышления
Странно, что не упомянули стандартный путь замены диапазона на список значений.


Проверил, оказывается сервер умеет преобразовывать between в список значений, а вот '<' не может.

create table test (a int, b int, c int, index(a,b));

MariaDB [test]> explain select * from test where a < 50 and b > 100\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
         type: range
possible_keys: a
          key: a
      key_len: 5
          ref: NULL
         rows: 36
        Extra: Using index condition
1 row in set (0.00 sec)

MariaDB [test]> explain select * from test where a between 1 and 49 and b > 100\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
         type: range
possible_keys: a
          key: a
      key_len: 10
          ref: NULL
         rows: 36
        Extra: Using index condition
1 row in set (0.00 sec)