Может ли мой запрос с несколькими операторами LIKE и REGEXP быть более эффективным?

Я создаю динамический запрос для выбора удаленных доменных имен из моей базы данных. На данный момент есть дюжина строк, но я собираюсь вскоре получить данные, которые будут содержать до 500000 строк.

Схема представляет собой одну таблицу, содержащую 4 столбца:

CREATE TABLE `DroppedDomains` (
  `domainID` int(11) NOT NULL AUTO_INCREMENT,
  `DomainName` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `DropDate` date DEFAULT NULL,
  `TLD` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`domainID`)
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Я не создал схему, это живая схема базы данных. Вот пример данных:

Я построил, вероятно, самый сложный тип запроса ниже. Критерии следующие:

ВЫБЕРИТЕ любое количество доменов, которые

  1. Начните со слова "начинается"
  2. Конец словом "заканчивается"
  3. Содержит слово "содержит это" в любом месте доменного имени
  4. Содержит слово "ContainsThisToo" в любом месте доменного имени
  5. Включить хотя бы одну цифру
  6. Имя домена должно содержать не менее 49 символов. Мультибайты должны учитываться как один символ (я использовал CHAR_LENGTH).
  7. Имя домена должно быть не менее 65 символов.
  8. TLD должен быть "org"
  9. DropDate должен быть позже 2009-11-01

Вот мой запрос до сих пор:

SELECT
*
FROM
DroppedDomains

WHERE

1=1

AND DomainName LIKE 'starts%ends'
AND DomainName LIKE '%containsThis%'
AND DomainName LIKE '%containsThisToo%'
AND DomainName LIKE '%-%'
AND DomainName REGEXP '[0-9]'
AND CHAR_LENGTH(DomainName) > 49
AND CHAR_LENGTH(DomainName) < 65
AND TLD = 'org'
AND DropDate > '2009-11-01'

Вот мои вопросы

  1. Будет ли это чрезвычайно полезным для производительности, учитывая, что у меня будет полмиллиона строк, если я сделаю TLD столбец свою таблицу и просто сделать TLD колонка внешний ключ к этому? Всего будет 5 ДВУ ( com, net, org, info, biz). Я понимаю, что в реальном мире существует больше ДВУ, но у этого приложения будет только 5. Пользователь не может указать свой ДВУ.

  2. я знаю это REGEXP и 500000 строк - это, вероятно, рецепт катастрофы. В любом случае я могу избежать REGEXP?

  3. Есть ли другие оптимизации для запроса, который я могу сделать? Как слияние LIKES или использовать другие функции, такие как, возможно, INSTR? И должен ли я реализовать какой-либо конкретный механизм кэширования?

Источник

2 ответа

Решение

Если у вас есть шаблон LIKE, который начинается с постоянного префикса, и у вас есть индекс для этого поля, тогда индекс можно использовать для быстрого поиска строк, начинающихся с префикса. К счастью, у вас есть именно такая ситуация:

AND DomainName LIKE 'starts%ends'

Если только несколько значений начинаются с starts тогда эти строки будут найдены очень быстро, а остальные выражения будут проверены только для этих строк. Вы можете проверить, что индекс используется, запустив EXPLAIN SELECT ...,

Источник

Вы должны запланировать создание индексов в соответствии с запросами, которые вы планируете использовать.

  • если у вас будут запросы, которые фильтруют только по DropDate, тогда будет полезен индекс DropDate.
  • если у вас будут запросы к этой группе по TLD, тогда будет полезен индекс по TLD.
  • если у вас есть запросы, которые выполняют поиск только по длине DomainName, вы можете рассмотреть возможность добавления поля DomainNameLength, которое имеет именно это (и индекс по этому), чтобы длина не вычислялась при каждом запуске запроса.
  • если у вас есть запросы, которые выполняют поиск (фильтрацию) по двум полям (например, TLD и DropDate), то вам, вероятно, понадобится индекс из двух столбцов для этих полей.
  • так далее...

Если ваш единственный запрос, который вы будете использовать, является сложным, о котором вы упомянули, то совет Марка (по поводу индекса для DomainName) - лучший.

По вопросу 1 о TLD поле:

Если у вас действительно будет только небольшое количество (например, 5) вариантов для этого, и вы не планируете использовать все доступные tlds, вы можете использовать ENUM тип.

CREATE TABLE(
   ....
   tld ENUM('com', 'net', 'org', 'info', 'biz')
)
Источник
Другие вопросы по тегам