MySQL: select * from...

[Paulo de Tarso F. M. 24]

Olá pessoal!

 

Uma dúvida, tenho uma base de registros com suas 10.000 linhas separadas em 50 e tra-lá-lás colunas. Qual é a melhor forma de montar uma query abaixo de uma forma mais otimizada? O host está enviando avisos de queries lentas, mas eu não sei como resolver...

SELECT * FROM tabela

As tabelas possuem PKs e evito usar o *, especifico todas as colunas que eu quero, mesmo que seja a tabela inteira em algumas situações, mas ainda assim costumo incluir todos os nomes, inclusive para poder usar um alias.

 

Diante disso, que outras boas práticas posso usar para amenizar esse problema? Reforço: preciso exportar a tabela inteira em alguns momentos, isso é fato, mas queria saber se existe alguma forma de melhorar esse processo.

 

Abraço!

[Alaerte Gabriel 662]

Dê uma lida em: http://imasters.com.br/artigo/22264/banco-de-dados/10-tecnicas-para-otimizacao-de-instrucoes-sql

 

e http://www.devmedia.com.br/otimizando-consultas-sql-em-mysql/5257

[Paulo de Tarso F. M. 24]

Obrigado Alaerte, vou ler os materiais indicados e qualquer coisa volto a escrever.

 

Obs.: Acho que o fórum mais adequado seria de MySQL, né? Não sei porque eu postei em PHP... rsrsrs... Apesar de eu montar os sistemas com PHP, nesse momento a minha dúvida se restringe apenas à questão do banco de dados.

[Paulo de Tarso F. M. 24]

Olha eu voltando com o assunto de otimizações... Alaerte, a criação de índices já foi feita, mas o problema ainda não foi resolvido... :(

 

Eu continuo com o problema de exportar a base inteira via PHP + MySQL, quando se está navegando pelas páginas, ok, a gente consegue paginar os resultados com LIMIT, mas e na hora de exportar a base inteira? Não tem como paginar porque os dados são exportados para um XLS. Então, o que fazer?

 

Hoje essa exportação é feita do jeito "tradicional" mesmo, que seria a query com os campos porém sem o LIMIT, aí o servidor reclama de queries lentas... :huh:

SELECT [campos] FROM [tabela] ORDER BY [id]

É possível executar isso de outra forma menos prejudicial?

 

Abraços

[lokaodomau 61]

Cola o EXPLAIN da SQL:

# Exemplo: se sua SQL é SELECT * FROM tabela
EXPLAIN SELECT * FROM tabela;

Assim a gente pode ver quais índices estão sendo utilizados.

[Paulo de Tarso F. M. 24]

Olá lokaodomau, segue o resultado:

+----+-------------+------------------+------+---------------+-----+---------+-----+------+----------------+  
| id | select_type | table            | type | possible_keys | key | key_len | ref | rows | Extra          |  
+----+-------------+------------------+------+---------------+-----+---------+-----+------+----------------+  
| 1  | SIMPLE      | [nome da tabela] | ALL  |               |     |         |     | 9903 | Using filesort |  
+----+-------------+------------------+------+---------------+-----+---------+-----+------+----------------+  

Essa tabela tem apenas uma chave primária, e ela foi usada no ORDER BY:

EXPLAIN SELECT * FROM [nome da tabela] ORDER BY idregistro;

[giesta 29]

Normalmente o * nao impacta tanto quanto as clausulas(where) e junções(join).

 

Poderia postar a consulta completa bem como o modelo er do banco?

[Paulo de Tarso F. M. 24]

Olá giesta, então a consulta completa é essa mesma, porque nós temos um sistema padrão no qual a maioiria das queries de exportação são assim, pegando tudo da tabela e ordenando por uma coluna específica.

 

Um detalhe, nem sempre o ORDER BY é pela PK da tabela, pode ser que seja isso... Temos relatórios onde o ORDER BY da exportação é pelo nome, outro pelo e-mail, outro pelo nome da empresa... Todos esses campos precisariam ser indexados? :blink:

 

Peguei uma tabela como exemplo e ela está assim:

CREATE TABLE `2015_visitantes` (
  `idvisitante` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `idioma` varchar(20) DEFAULT '0',
  `categoria` varchar(50) DEFAULT NULL,
  `processo_seletivo` varchar(20) DEFAULT NULL,
  `nome` varchar(100) DEFAULT NULL,
  `cpf` varchar(15) DEFAULT NULL,
  `email` varchar(100) DEFAULT NULL,
  `sexo` varchar(20) DEFAULT NULL,
  `datanasc` date DEFAULT NULL,
  `cep` varchar(20) DEFAULT NULL,
  `endereco` varchar(50) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `numero` varchar(20) DEFAULT NULL,
  `complemento` varchar(50) DEFAULT NULL,
  `bairro` varchar(50) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `cidade` varchar(50) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `estado` varchar(50) DEFAULT NULL,
  `pais` varchar(50) DEFAULT NULL,
  `situacao_inscricao` varchar(30) DEFAULT NULL,
  `situacao_final` varchar(30) DEFAULT NULL,
  `promocode` varchar(15) DEFAULT NULL,
  `historico` text,
  `obs` text,
  `headers` text,
  `datainsc` datetime DEFAULT NULL,
  PRIMARY KEY (`idvisitante`),
  KEY `idx_1` (`cpf`,`processo_seletivo`),
  KEY `idx_2` (`email`,`processo_seletivo`)
) ENGINE=MyISAM AUTO_INCREMENT=20159 DEFAULT CHARSET=latin1;

[giesta 29]

Uma duvida.

 

A performance da consulta sem Order By é mto superior a consulta com Order By? Para responder não execute uma atras da outra senao ele vai dar hit. De preferencia reset o banco ou a maquina , ou ainda desligue o hit.

 

E respondendo a sua pergunta. Sim. Um indice irá melhorar sensivelmente a performance desde que a ordenação seja pedida pelo mesmo campo do indice.

Fazer um indice para cada caso pode parecer exagerado, mas dependendo do caso pode ser util.

 

Vale lembrar que a presença de um ou mais indices/chaves impacta o tempo inserção de novas linhas.

[Paulo de Tarso F. M. 24]

Fiz as duas consultas, seguem os resultados:

 

SELECT * FROM 2015_visitantes
Time: 8.398ms

 

SELECT * FROM 2015_visitantes ORDER BY idvisitante
Time: 8.571ms

:huh:

[giesta 29]

Quantos milhões de linhas tem nessa sua tabela?

[Paulo de Tarso F. M. 24]

Não tem nem 10.000... ¬¬

 

Nossas tabelas não costumar ser grandes não, para você entender, trabalhamos com credenciamento de eventos e cada evento possui uma base diferente. Estamos trabalhando em um evento agora no qual recebemos uma base prévia com mais de 400.000 registros, esse foi o maior que vi até hoje, mas é a exceção, a maioria dos eventos não passa de 10.000, 15.000 registros.

[giesta 29]

todas as consultas estao lentas ou apenas essa?

 

Quantos campos tem nessa coluna? experimente trocar o * por apenas o q vc realmente precisa...

[Paulo de Tarso F. M. 24]

São só essas queries onde temos que exportar a base inteira, de uma maneira geral isso não acontece sempre, é somente quando alguém está acessando a nossa área administrativa e precisa exportar tudo...

 

Bom, tentei executar a query com todas as colunas da tabela ao invés do * e olha só o que aconteceu:

 

Time: 44.682ms

:wacko:

 

Não sei se isso pode influenciar, mas a conexão com a internet aqui na empresa hoje está beeeeeeeem lenta...A mesma query anterior está demorando 34 segundos...