MySQL: select * from...

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

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.

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

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.

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;

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?

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;

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.

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:

Quantos milhões de linhas tem nessa sua tabela?

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.

todas as consultas estao lentas ou apenas essa?

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

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...