MySQL проблемы с текстовыми соответствиями с использованием оператора IN

У меня большая таблица (2 миллиона строк) с колонкой, содержащей текстовые идентификаторы (это латинские названия видов, Homo_sapiens, Tyranosaurus_rex и т. Д.)

У меня есть другая таблица, содержащая латинские названия и "общие" названия видов, и я могу запросить это, чтобы получить небольшую подборку (~140 имен) латинских имен, некоторые из которых отображаются в первой таблице. Я хочу получить строки в первой таблице, имена которых отображаются именно на этот небольшой выбор. Запрос, который я использую, чтобы получить небольшой выбор (всего 140 строк), выполняется быстро, так как общее имя имеет индекс MySQL "FULLTEXT"

select distinct latin_name from common_names_table 
  where match(common_name) against('+*mo*' in boolean mode)

Но тогда, если я попытаюсь использовать SQL IN оператор, чтобы сопоставить их в большой, 2 миллиона строк таблицы, это займет много минут,

select latin_name,popularity from big_table 
 where latin_name in (
  select distinct latin_name from common_names_table 
    where match(common_name) against('+*mo*' in boolean mode)
  )
 ORDER BY popularity DESC LIMIT 50;

Это правда, хотя я установил как полный текст, так и обычный индекс в столбце latin_name.

CREATE FULLTEXT INDEX name_fulltext_index ON big_table (latin_name);
CREATE INDEX name_index          ON big_table (latin_name);

Как я могу ускорить это? Есть ли проблема с использованием IN оператор с индексированными текстовыми полями? Если да, есть ли какой-то особый вид индекса "точного соответствия", который я могу использовать для текстовых полей? Поля latin_name имеют тип "VARCHAR" и максимальную длину 190 в маленькой таблице и 200 в большой, если это имеет значение.

Спасибо за любую помощь

По запросу - вот определения таблицы:

CREATE TABLE `big_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent` int(11) NOT NULL,
  `latin_name` varchar(200) DEFAULT NULL,
  `popularity` double DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `pop_index` (`popularity`),
  KEY `name_index` (`latin_name`),
  FULLTEXT KEY `name_fulltext_index` (`latin_name`)
) ENGINE=InnoDB AUTO_INCREMENT=1781766 DEFAULT CHARSET=utf8;

CREATE TABLE `common_name_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `latin_name` varchar(190) CHARACTER SET utf8mb4 NOT NULL DEFAULT '',
  `common_name` varchar(190) CHARACTER SET utf8mb4 NOT NULL,
  PRIMARY KEY (`id`),
  KEY `name_index` (`latin_name`),
  FULLTEXT KEY `common_name_index` (`common_name`)
) ENGINE=InnoDB AUTO_INCREMENT=2024 DEFAULT CHARSET=utf8;
Источник

3 ответа

Решение

АГА - благодаря @krishKM, который попросил дать определения, я нашел проблему. Кодировка набора символов для двух столбцов, которые я пытаюсь сопоставить, отличается: один - это код UTF8 по умолчанию в mySQL, другой - "правильная" 4-байтовая кодировка utf8mb4.

Если я установлю одинаковое кодирование символов latin_name в обеих таблицах, запрос займет ~20 миллисекунд вместо 5 минут.

Источник

Вы можете попробовать объединение вместо 'IN':

select
b.latin_name,
b.popularity
from
(
    select distinct latin_name from common_names_table 
    where match(common_name) against('+*mo*' in boolean mode)
) a
left join big_table as b on (a.latin_name=b.latin_name)
where b.latin_name IS NOT NULL
ORDER BY b.popularity DESC LIMIT 50;

Левое соединение (где правая сторона не равна нулю), вероятно, будет быстрее, чем внутреннее соединение

Источник

LEFT не обязательно:

select  b.latin_name, b.popularity
    from  
    (
        SELECT  distinct latin_name
            from  common_names_table
            where  match(common_name) against('+*mo*' in boolean mode) 
    ) cn
    join  big_table as b  ON (cn.latin_name = b.latin_name)
    ORDER BY  b.popularity DESC
    LIMIT  50;

Чтобы понять, почему это медленно, выполните

        SELECT  COUNT(distinct latin_name)
            from  common_names_table
            where  match(common_name) against('+*mo*' in boolean mode);

Это много строк нужно найти в big_table до сортировки и ограничения.

Источник
Другие вопросы по тегам