Temos uma tabela de índice de pesquisa, fornecida por um CMS, que tem o formato:
CREATE TABLE `craft_searchindex` (
`elementId` int NOT NULL,
`attribute` varchar(25) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
`fieldId` int NOT NULL,
`locale` char(12) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
`keywords` text CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
PRIMARY KEY (`elementId`,`attribute`,`fieldId`,`locale`),
FULLTEXT KEY `craft_searchindex_keywords_idx` (`keywords`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci
Como parte da funcionalidade de pesquisa fornecida pelo CMS, é formada a seguinte consulta:
SELECT * FROM `craft_searchindex` WHERE (`keywords` LIKE '% tom %') AND `locale` = 'en_au' AND `elementId` IN (<ids>)
onde idsé uma lista filtrada de elementIds com base em uma consulta anterior.
O que observamos é que quando atingimos um determinado número de itens naquela INcláusula, o desempenho cai e o tempo de execução da consulta torna-se constante, conforme mostrado em um gráfico coletado antes da migração para o InnoDB (tempo de execução do eixo y em milissegundos, x- número do eixo de itens na INcláusula, série min/média/máx):
A última linha de uma EXPLAIN ANALYZEconsulta mostra o seguinte para aproximadamente 33.500 itens:
-> Index range scan on craft_searchindex using PRIMARY over (elementId = 1) OR (elementId = 128) OR (33500 more) (cost=55355 rows=234514) (actual time=0.0376..537 rows=469028 loops=1)
e para aproximadamente 34.000 itens:
-> Table scan on craft_searchindex (cost=335885 rows=3.27e+6) (actual time=0.0303..3237 rows=3.72e+6 loops=1)
Minha interpretação disso (que está de acordo com o gráfico) é que:
- até certo limite, o índice de chave primária é verificado (um por um, fornecendo o crescimento linear) e
- além desse limite, a tabela completa é sempre verificada (fornecendo o valor constante)
Minha pergunta é, dado que há uma diferença significativa entre o tempo de execução imediatamente abaixo desse limite e o tempo acima dele, por que o planejador de consultas opta por abandonar a verificação do índice[*], e algo pode ser feito a respeito (com a restrição que a preparação da consulta é realizada pelo CMS, portanto, na maior parte fora do nosso controle)?
Ingressando em uma mesa temporária
Embora não seja uma solução para nós, dadas as restrições das consultas tratadas pelo CMS, segui a sugestão de @Akina de usar uma tabela temporária indexada. Isso altera a consulta de (efetivamente):
SET SESSION group_concat_max_len = 1 << 19;
PREPARE ids_stmt FROM 'SELECT GROUP_CONCAT(`id`)
INTO @ids_clause
FROM (
SELECT `elements`.`id`
FROM `craft_elements` `elements`
JOIN `craft_elements_i18n` `elements_i18n` ON elements_i18n.elementId = elements.id
JOIN `craft_content` `content` ON content.elementId = elements.id
JOIN `craft_users` `users` ON users.id = elements.id
WHERE ((elements_i18n.locale = ?) AND (content.locale = ?)) AND (elements.archived = 0)
) AS `ids`';
SET @l = 'en_au';
EXECUTE ids_stmt USING @l, @l;
DEALLOCATE PREPARE ids_stmt;
SET @search_query = CONCAT("SELECT `craft_searchindex`.* FROM `craft_searchindex` WHERE (`keywords` LIKE '% tom %') AND `locale` = 'en_au' AND `elementId` IN (", @ids_clause, ')');
PREPARE search_stmt FROM @search_query;
EXECUTE search_stmt;
DEALLOCATE PREPARE search_stmt;
para:
PREPARE ids_stmt FROM 'CREATE TEMPORARY TABLE ids_table
(PRIMARY KEY(`id`))
SELECT `elements`.`id`
FROM `craft_elements` `elements`
JOIN `craft_elements_i18n` `elements_i18n` ON elements_i18n.elementId = elements.id
JOIN `craft_content` `content` ON content.elementId = elements.id
JOIN `craft_users` `users` ON users.id = elements.id
WHERE ((elements_i18n.locale = ?) AND (content.locale = ?)) AND (elements.archived = 0)
LIMIT 1';
SET @l = 'en_au';
EXECUTE ids_stmt USING @l, @l;
DEALLOCATE PREPARE ids_stmt;
SELECT `craft_searchindex`.*
FROM `craft_searchindex`
JOIN `ids_table` ON `craft_searchindex`.`elementId` = `ids_table`.`id`
WHERE (`keywords` LIKE '% tom %') AND `locale` = 'en_au';
e isso mantém o crescimento linear do tempo de execução bem além do limite anterior.
Curiosamente (e talvez porque a tabela temporária tenha apenas uma coluna) a eliminação da chave primária não afeta o tempo de execução.
Abaixo está um gráfico comparando estes (os tempos agora estão em segundos):
[*]: A partir de alguns cálculos breves, continuar a varredura do índice em vez de realizar uma varredura completa da tabela ainda estaria abaixo do tempo de execução da varredura completa da tabela até que o número de itens na INcláusula excedesse aproximadamente 200.000