As subconsultas são executadas muito rapidamente individualmente, mas quando unidas são muito lentas

ypercube resolveu o problema. As subconsultas eram totalmente desnecessárias e tudo funciona com junções simples. Ainda é estranho que o otimizador do MySQL não possa usar minha consulta original. Veja abaixo a pergunta e muitos detalhes. Além de uma solução completa na parte inferior da minha pergunta. É baseado na resposta do ypercube.

Cada subconsulta é muito rápida, bem menos de 1 segundo. As 5-6 subconsultas são unidas (algumas LEFT, algumas INNER) e o tempo aumenta para 400 segundos.

A consulta geral que estou usando para teste retorna apenas 441 linhas.

Tentei colocar cada uma das subconsultas em uma consulta "CREATE TABLE". Cada um foi feito em bem menos de 1 segundo. Em seguida, refiz a consulta externa usando as tabelas recém-criadas e ela também foi executada em menos de 1 segundo. Portanto, não há nenhum problema real com as junções. Eu coloquei índices idpara minhas tabelas criadas. Todas as tabelas são unidas em correspondência id= id.

Como posso fazer o MySQL executar a consulta com eficiência? Devo usar tabelas temporárias? Eu já escrevi um monte de código PHP para reunir as várias junções de subconsulta, então prefiro apenas descobrir como fazer isso funcionar, se possível.

Tentei usar a palavra-chave "STRAIGHT_JOIN" e remover o arquivo ORDER BY. Isso reduziu o tempo de consulta para 90s. Mas eu deveria estar recebendo 1s max.

Eu tentei STRAIGHT_JOINcom ORDER BYe demorou 235 segundos. Portanto, parece que o exterior ORDER BYé um grande problema de desempenho.

EDITAR:

Testado usando tabelas temporárias. A consulta é executada muito rapidamente. Mas deve haver uma maneira de fazer o mysql fazer isso tão rápido com JOINS.

Além disso, o log de consulta lento mostra:

Rows_examined: 484006914

484 milhões de linhas parecem um produto cartesiano. Por que está examinando tantas linhas?

A consulta tem esta estrutura:

SELECT t0.`id`, t1.`length`, t2.`height`, t3.`family`
FROM
`products` t0
INNER JOIN
(
SELECT t1.`id`, t2.`value` AS `length`
FROM `products` t1
INNER JOIN `product_eav_decimal` t2
ON t1.`id` = t2.`product_id`
WHERE t2.`attribute_id` = 91
AND t2.`value` BETWEEN 15 AND 35
) t1

ON t0.`id` = t1.`id`

LEFT JOIN
(
SELECT t1.`id`, t2.`value` AS `height`
FROM `products` t1
INNER JOIN `product_eav_decimal` t2
ON t1.`id` = t2.`product_id`
WHERE t2.`attribute_id` = 80
# no other conditions
) t2
ON t0.`id` = t2.`id`

INNER JOIN
(
.
.
.
) t6
ON t0.`id` = t6.`id`
ORDER BY t0.`id` ASC

...etc LEFT JOINS são usados ​​quando não há outras condições na subconsulta além do attribute_id. INNER JOIN usado quando há alguma outra condição. Isso cria um resultado de pesquisa válido. A consulta funciona, leva apenas 400 segundos em vez de 0,04.

Se ninguém souber como fazer a sintaxe JOIN funcionar, usarei tabelas temporárias, pois isso parece funcionar.

TABELAS:

1.) produtos

CREATE TABLE `products` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `sku` varchar(127) NOT NULL COMMENT '3char vencode + model',
 `model` varchar(127) NOT NULL,
 `vendor_id` int(11) DEFAULT NULL,
 `updated` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`),
 UNIQUE KEY `sku` (`sku`),
 KEY `model` (`model`),
 KEY `vendor_id` (`vendor_id`),
 CONSTRAINT `FK1` FOREIGN KEY (`vendor_id`) REFERENCES `vendors` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=153282 DEFAULT CHARSET=utf8

2.) decimais

CREATE TABLE `product_eav_decimal` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `product_id` int(11) NOT NULL,
 `attribute_id` int(11) DEFAULT NULL,
 `value` decimal(11,3) DEFAULT NULL,
 `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`),
 UNIQUE KEY `natural_key` (`product_id`,`attribute_id`,`value`),
 UNIQUE KEY `product_id_2` (`product_id`,`attribute_id`),
 KEY `last_update` (`last_update`),
 KEY `product_id` (`product_id`),
 KEY `attribute_id` (`attribute_id`),
 KEY `value` (`value`),
 CONSTRAINT `FK1` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `FK2` FOREIGN KEY (`attribute_id`) REFERENCES `attributes` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=370772 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

3.) varchar (faz referência a outra tabela, values_varchartabela para valores varchar reais)

CREATE TABLE `product_eav_varchar` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `product_id` int(11) DEFAULT NULL,
 `attribute_id` int(11) DEFAULT NULL,
 `value_id` int(11) DEFAULT NULL,
 `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`),
 UNIQUE KEY `natural_key` (`product_id`,`attribute_id`,`value_id`),
 KEY `last_update` (`last_update`),
 KEY `product_id` (`product_id`),
 KEY `value_id` (`value_id`),
 KEY `attribute_id` (`attribute_id`),
 CONSTRAINT `FK1` FOREIGN KEY (`value_id`) REFERENCES `values_varchar` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `FK2` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `FK3` FOREIGN KEY (`attribute_id`) REFERENCES `attributes` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=86049 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

Adaptado da resposta do ypercube:

SELECT t0.id, 
       t1.`value` AS length, 
       t2.`value` AS height, 
       t3.`value` AS family,
       t5.`value` AS type
FROM
  products t0

INNER JOIN # INNER used when search criteria
# length (only searched values)
  product_eav_decimal t1
    ON  t1.product_id = t0.id  
    AND t1.attribute_id = 91
    AND t1.`value` BETWEEN 15 AND 35 # search criteria

LEFT JOIN # LEFT used when no search criteria
# height (all, including blank/null)
  product_eav_decimal t2
    ON  t2.product_id = t0.id  
    AND t2.attribute_id = 80  

LEFT JOIN  # LEFT - no search critera
# family - varchar type requires extra join to values table
  product_eav_varchar t3
    ON  t3.product_id = t0.id  
    AND t3.attribute_id = 77
LEFT JOIN # LEFT join to values table matches eav table join
values_varchar t4
    ON t3.value_id = t4.id
# search criteria would be here. see next

INNER JOIN # INNER - search criteria below
# type - varchar requires extra join, see below
  product_eav_varchar t5
    ON t5.product_id = t0.id
    AND t5.attribute_id = 76
INNER JOIN # INNER join to values table matches eav table join
values_varchar t6
    ON t5.value_id = t6.id
    # search criteria
    AND (t6.value LIKE "%sofa%" COLLATE utf8_general_ci OR t6.value LIKE "%chair%" COLLATE utf8_general_ci)

ORDER BY t0.id ASC;

A consulta funciona. Ele é executado em alguns milissegundos. Se forem fornecidos termos de pesquisa ou limites de intervalo, ele retornará APENAS resultados correspondentes, usando INNER JOINs. Onde não há critérios, ele usa LEFT JOINs para retornar qualquer valor (incluindo NULL/em branco).

Atualização de agosto de 2014 - agora existem 400-500 mil linhas na productstabela e o estilo de consulta usado acima ainda funciona muito rápido. Parece que as junções são muito mais rápidas que as subconsultas no MySQL.