Lentidão em consulta em tabela com grande número de registro

[Danilo C]

Olá, possuo uma tabela onde diariamente são adicionados de 30 a 50 mil registros.

Hoje ela possui por volta dos 1 milhão de registros.

Na página inicial do meu site, eu exibo 12 registros dessa tabela. Os 12 ultimos inseridos:

SELECT a, b, c FROM tabela WHERE 1 LIMIT 12 ORDER BY date DESC

Certo?

Acontece que a página fica EXTREMAMENTE lenta com isso, chegando a dar erro no sistema (erro 500, se não me engano) quando tem muitos usuários acessando.

Imagino que seja devido ao alto processamento/memória exigido do processador.

(Acho que o campo data não é indexado. Vou conferir mais tarde. Mas a dúvida/problema talvez ainda permaneça.)

Como melhorar a velocidade da consulta?

Como melhor configurar o mysql para tal situação?

Acho que vale salientar que chego a ter mais de 100 usuários acessando simultaneamente.

Edited December 3, 2012 by Danilo C

[Denis Courcy]

...

Como melhorar a velocidade da consulta?

Como melhor configurar o mysql para tal situação?

Acho que vale salientar que chego a ter mais de 100 usuários acessando simultaneamente.

O primeiro passo é criar um índice por data descendente e modificar sua consulta para fazer o abaixo:

SELECT a, b, c 
FROM tabela use index (nome-do-indice-com-data-invertida)
LIMIT 12

As demais perguntas responderemos se esta ação não for suficiente.

[Danilo C]

Não conhecia o truque do index desc.

Uma coisa que esqueci de dizer, que na verdade era a mais importante, é que a tabela dava join em outras.

from table_a

natural join b

natural join c

natural join d

Ao remover o join d, a consulta passou de 16 segundos para pouquissimos milesimos.

O que eu fiz foi deixar só o join b, e então, na aplicação, fazer outro select, já que eu estava pegando limit 12, para pegar os dados da tabela c e d.

Vou tentar o truque do index descendente pra ver o quanto melhora.

[Denis Courcy]

Não conhecia o truque do index desc.

Uma coisa que esqueci de dizer, que na verdade era a mais importante, é que a tabela dava join em outras.

from table_a

natural join b

natural join c

natural join d

Ao remover o join d, a consulta passou de 16 segundos para pouquissimos milesimos.

O que eu fiz foi deixar só o join b, e então, na aplicação, fazer outro select, já que eu estava pegando limit 12, para pegar os dados da tabela c e d.

Vou tentar o truque do index descendente pra ver o quanto melhora.

A falha do join é porque ele esta gerando um TABLE SCAN (busca linear sequencial em cada tabela do join). Isto acontece essencialmente po falta de índice que amarre uma tabela a outra.

[MatheusRomagna]

Muito certo, isto mesmo que o Denis Courcy relatou.

Você não tem Indices de ligações, tem que ter Indices para que fique mais rapida as consultas assim ligando os JOIN TABLE1 f on f.ID1 = ID2.