Mysql Улучшение эффективности поиска с помощью подстановочных знаков (%%)

Ниже приведен запрос, который я использую для поиска человека по электронной почте

  SELECT *
    FROM phppos_customers
    JOIN phppos_people ON phppos_customers.person_id = phppos_people.person_id
   WHERE deleted = 0
     AND email LIKE '%f%'
ORDER BY email ASC

Будет ли добавление индекса в "email" ускорить запрос?

Ответ 1

Нет, потому что MySQL не сможет использовать индекс, если у вас есть главный шаблон. Если вы изменили свой LIKE на "f%", тогда он сможет использовать индекс.

Ответ 2

Нет, Mysql не будет использовать индекс, потому что аргумент LIKE (%f%) начинается с символа подстановки %. Если он начинается с константы, будет использоваться индекс.

Дополнительная информация: 7.5.3. Как MySQL использует индексы

Ответ 3

Подстановочная левая сторона операции LIKE гарантирует, что индекс, если он существует в столбце email, не может использоваться.

Полнотекстовый поиск (FTS) является предпочтительным синтаксисом для поиска строк в тексте через SQL. У MySQL есть собственные функции FTS, используя синтаксис MATCH/AGAINST (требуется, чтобы таблица использовала механизм MyISAM для версии 5.5 и ниже. InnoDB FTS поддерживается на v. 5.6+):

  SELECT c.*, p.*
    FROM PHPPOS_CUSTOMERS c
    JOIN PHPPOS_PEOPLE p ON p.person_id = c..person_id
   WHERE deleted = 0
     AND MATCH(email) AGAINST('f')
ORDER BY email

Но есть сторонние технологии FTS, такие как Sphinx.

Ответ 4

В моем сообщении я подробно описываю технику, которая позволяет использовать индекс с LIKE для быстрого %infix% поиска, за счет стоимости дополнительного хранилища:

fooobar.com/questions/348157/...

Пока строки являются относительно небольшими, требование хранения обычно приемлемо.

Согласно Google, средний адрес электронной почты 25 длинный. Это увеличивает требуемое хранилище в среднем на 12.5 и дает вам быстрый индексный поиск взамен. (См. Мой пост для расчетов.)

С моей точки зрения, если вы храните 10 000 адресов электронной почты, вы также должны хранить (эквивалент) около 100 000 адресов электронной почты. Если это то, что требуется, чтобы вы могли использовать индекс, это кажется приемлемым компромиссом. Часто дисковое пространство дешево, а неиндексированные поиски недоступны.

Если вы решите воспользоваться этим подходом, я предлагаю ограничить длину ввода адресов электронной почты на 64. Эти редкие (или атакующие) адреса электронной почты такой длины потребуют до 32 раз обычного хранилища. Это дает вам:

Защита от злоумышленника, пытающегося наполнить вашу базу данных, поскольку они все еще не очень впечатляющие количества данных.
Ожидается, что большинство адресов электронной почты не имеют такой длины.

Если вы считаете, что символы 64 слишком жесткие, используйте 255 вместо этого для наихудшего коэффициента увеличения хранилища 127.5. Смешной? Возможно. Вероятно? Нет. Быстро? Очень.

Ответ 5

Вы не сможете сделать это быстрее с помощью LIKE, как все говорят (о % в начале), но вы можете немного улучшить его, присоединившись после того, как вы сначала фильтруете своих людей.

SELECT *
  FROM (SELECT * 
          FROM `phppos_customers`
         WHERE `deleted` = 0
           AND  `email`  LIKE '%f%') `t_customers`
  JOIN `phppos_people` ON `t_customers`.`person_id`=`phppos_people`.`person_id`
 ORDER BY `email` asc

Ответ 6

Я знаю, как перехитрить MySQL и включить поиск по индексу, даже если поиск с подстановочными знаками с левой стороны. Просто создайте перевернутый столбец вашего столбца (сделайте его индексом), поменяйте местами строку поиска и используйте подстановочный знак справа, который имеет поддержку индекса.

Так что, если у вас есть в базе данных слово "slibro" и вы хотите выполнить поиск "% libro", созданный обратный столбец будет содержать "orbils", а поиск будет "orbil%".

PS: Но у меня нет решения, как сделать быстрый полный поиск по шаблону "% x%", хотя :).