MySQL 5.5.28 no Ubuntu 12.04
Se o resultado for mais longo do que group_concat_max_lenentão, o resultado será truncado sem graça.
Atualmente eu tenho um script que tenta verificar o comprimento necessário antes do tempo e define group_concat_max_lenpara ser grande o suficiente.
Mas a verificação adiciona consultas extras. Existe alguma desvantagem em apenas definir group_concat_max_leno valor máximo? A vantagem é menos consultas.
Não estou muito familiarizado com o SQL Server, mais familiarizado com o MySQL.
Estou acostumado a ver tudo em código, enquanto no SQL Server, algumas coisas são feitas com botões. No MySQL, eu executaria USE testing_databasee saberia que minhas consultas subsequentes seriam executadas nesse banco de dados.
No SQL Server Management Studio, vejo que meu "Banco de Dados de Teste" está selecionado na seleção suspensa. Isso significa que as consultas no script serão executadas SOMENTE nesse banco de dados?
O script não inclui nenhuma referência a nomes de banco de dados. Ele é usado sp_msdependenciespara preencher uma lista de exibições e, em seguida, atua nessa lista. Será sp_msdependenciesexecutado apenas no banco de dados atual?
Existem vários outros bancos de dados além do banco de dados de teste que não quero tocar neste teste.
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.
Eu tenho uma consulta que usa:
t3.`value` LIKE "%someval%" COLLATE utf8_general_ci
Eu carrego a página no meu navegador e recebo este erro impresso na tela:
COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'latin1'
A consulta também é repetida na página. E se eu copiar e colar a consulta gerada em uma janela SQL do phpMyAdmin, ela funcionará bem, produzindo o resultado esperado.
Eu tive um problema anterior em que o PHP não podia usar LOAD DATA INFILE. Eu me pergunto se este é um tipo de problema semelhante. Mas não consigo ver como seria.
Isso está sendo executado em uma pilha MySQL Apache PHP do Ubuntu Server 10.04.
Nova instalação do CentOS.
Eu estava executando uma importação de um banco de dados grande (arquivo sql de 2 GB) e tive um problema. O cliente SSH parecia perder a conexão e a importação parecia congelar. Eu usei outra janela para fazer login no mysql e a importação parecia estar morta, presa em uma tabela de linhas de 3M específica.
Então eu tentei
DROP DATABASE huge_db;
15-20 minutos depois, nada. Em outra janela, fiz:
/etc/init.d/mysqld restart
A janela DROP DB apresentou a mensagem: SERVER SHUTDOWN. Então eu realmente reiniciei o servidor físico.
Logado novamente no mysql, verificado e o db ainda estava lá, executei
DROP DATABASE huge_db;
novamente, e novamente estou esperando já cerca de 5 minutos.
Mais uma vez, é uma nova instalação. O huge_dbé o único db (diferente do dbs do sistema). Eu juro que deixei cair db's tão grandes antes e rapidamente, mas talvez eu esteja errado.
Eu derrubei com sucesso o banco de dados. Demorou algo como 30 minutos. Observe também que acho que me enganei quando pensei que a importação do mysqldump estava morta. A conexão do terminal foi perdida, mas acho que o processo ainda estava em execução. Eu provavelmente matei a tabela intermediária de importação (a tabela de linhas de 3M) e provavelmente 3/4 do caminho através de todo o banco de dados. Era enganoso que "top" mostrasse o mysql usando apenas 3% da memória, quando parecia que deveria estar usando mais.
Soltar o banco de dados acabou levando 30 minutos, então, novamente, talvez eu não tivesse que reiniciar o servidor e possivelmente poderia ter esperado o DROP terminar, mas não sei como o mysql reagiria ao obter uma consulta DROP para o mesmo db que está importando via mysqldump.
Ainda assim, a pergunta permanece, por que leva mais de 30 minutos para DROP um banco de dados de 2 GB quando tudo o que deveria fazer é excluir todos os arquivos db e remover todas as referências ao banco de dados de information_schema? Qual é o problema?