Select com subquery lento

[Quati Maldito 0]

Olá!

 

Tenho um programa em ASP, acessando mysql com essa query:

 

select id,campo1,campo2,campo3,campo4,campo5,campo6, fk_tab2 from tab1 where campo1 = 1 and campo2 = 0 and campo3 = 0 and fk_tab2 IN(SELECT id FROM tab2 where campo1_tab2 = 1 and campo2_tab2 = 0) order by id desc

 

Funciona perfeitamente, mas é lento.

 

Alguém sabe de uma alternativa a essa query afim de deixar mais rápido?

 

Obrigado

[Anderson Schmitt 0]

Já tentou o INNER JOIN?

 

SELECT t1.id, t1.campo1, t1.campo2, t1.campo3, t1.campo4, 
t1.campo5, t1.campo6, t1.fk_tab2 
FROM tab1 AS t1
INNER JOIN tab2 AS t2 ON t1.fk_tab2 = t2.id
WHERE t1.campo1 = 1 
and t1.campo2 = 0 
and t1.campo3 = 0 
and t2.campo1_tab2 = 1 
and t2.campo2_tab2 = 0

 

Atenciosamente,

 

Anderson Schmitt

[xanburzum 169]

na verdade essa string SQL , nao é daquelas, que podemos dizer, que esta consumindo performance do server,

mas sugiro verificar como esta os relacionamento e os dados que esta recuperando, você precisa ter uma relacionamento entre as tabelas, na verdade, a impôrtancia de uma modelagem de dados bem definida até os mínimos detalhes, como os campos de relacionamentos, um velho ditado diz, que um relacionamento bem definido é a alma do aplicativo, pois daí saem todas as consultas e operações com o banco, influenciando diretamente na otimização e performance do aplicativo

[jrcardozo 4]

Primeiramente você precisa fazer uma analise da consulta utilizando algum software de gerenciamento de banco de dados

 

Você precisa avalia aquantidade de registros e tamanho em mb de cada tabela

 

Verificar chaves primárias e estrangeiras

 

Criar indices de acordo com as clausuas das consultas

 

Fazer testes alterando a forma de escrit ada consulta, utilizando IN , EXISTS, INNER ou até mesmo criando alguma função para aliviar o banco de dados durante o processamento

[xanburzum 169]

Otimização de queries geralmente se tornam um pouco confusas, por isso algumas vezes é um desafio para o programador. Muitos têm a idéia de que consultas rápidas são as que possuem menos códigos SQL, errado. Uma consulta que tiver performance, qualidade, com certeza vai ter desempenho. Esse é o foco do artigo.

Hoje em dia se dissiparmos mais o assunto e abordarmos gerenciamento de performance, existem ferramentas que medem o tempo de execução da sua execução sql, algumas delas pode-se ser vista pelo próprio site do MySql, acessando http://dev.mysql.com/downloads/, ou até mesmo por uma aplicação de gerenciamento MySql, utilizando a aplicação do mySql Monitor, é possível visualizar e realizar uma avaliação de como está o desempenho, mas estas especificações ficam para um próximo artigo.

 

Exemplo:

Suponhamos que temos duas tabelas, CLIENTE e VENDAS. Necessitamos selecionar todas as vendas do determinado cliente:

 

SELECT vendas.*

FROM cliente a INNER JOIN vendas b

WHERE a.# = ‘RODRIGO DE SOUZA’;

 

Este exemplo requer necessidades especiais, e também potencial. Pois imaginamos que as tabelas contenham 70 registros na tabela cliente e 20.000 registros na tabela vendas. Teríamos o seguinte produto cartesiano para abordar apenas os dados necessários: 70 x 20.000 = 1.400.000. Este valor são todos os registros distintos que podem ser encontrados na consulta, sendo que estamos falando apenas de 20.070 registros no banco de dados.

Suponhamos que o cliente RODRIGO DE SOUZA tenha efetuado apenas 35 compras das tantas registradas; como as execuções SQL iniciam-se de baixo para cima, podemos dizer que restringimos mais rapidamente os resultados, pois são poucos os registros com este cliente. Porém, a leitura continuará sendo feita por todos os 1.400.000 registros, após execução, resultará apenas 35 tuplas encontradas.

 

 

Uso de Índices

Certamente, com estas bases de registros, se fossemos realizar a consulta anterior, varrendo os 1.400.00 registros para gerar um historio do cliente das compras compradas por ele, seria custoso, para solucionar este pequeno problema é recomendado o uso de índices, pois com a criação e utilização, o mesmo possibilita o acesso direto às propriedades necessárias da tabela.

De acordo com a álgebra relacional estamos selecionando os registros baseados na pesquisa das propriedades informadas como filtro. Sendo que os registros não possuem nenhuma ordem específica (com exceção da tabela com campo auto-incremento), os mesmos são alocados conforme ordem cronológica de inserção.

Os índices são estruturas opcionais que estão associadas à tabela, sendo que os mesmo aumentam o desempenho na recuperação dos dados. Baseia-se na estruturação de dados onde os mesmos estão organizados mediante alguma propriedade mencionada, ordenados segundo algum critério.

 

 

Dicas para criação:

* Crie preferencialmente nas colunas que são inteiros (integer), aumenta o desempenho comparado aos valores varchar, valores de inteiro geralmente tem tamanho menor e são muito mais rápidos para comparar, conseqüentemente ocupará menos espaço na memória para o processamento.

* Crie índices para as colunas de chave estrangeira que serão utilizadas nas clausulas where.

* Considere a criação baseando-se nas colunas freqüentemente usadas nas cláusulas where, order by, group by.

* Não crie índices com possíveis valores duplicados. Ex: Sexo (Masculino ou Feminino).

* Criar índices ao unir duas tabelas na cláusula where (a.id = b.id), caso contrário sua consulta ficará lenta, e será cada vez mais custosa na medida em que o número de registros aumentarem em sua base de dados.

* Crie índices com várias colunas de sua tabela.

 

Comandos:

Criar índice:

CREATE INDEX ON (, [ ] );

 

Mostrar índices de uma tabela:

SHOW INDEX FROM ;

 

Excluir índice:

DELETE INDEX ON ;

 

Se mesmo assim algum índice não estiver ajudando, ou se preferir verificar a real necessidade das colunas de sua tabela para criá-las, utilize a explicação das consutas sql:

EXPLAIN ;

 

Criei um índice, e agora?

Para fazer o uso do mesmo e melhorar o desempenho de sua consulta, uma das alternativas é disponibilizar esta propriedade na cláusula WHERE de seu SQL.

Não há regras de inserir índices somente em cláusulas WHERE, pelo contrário, é recomendado criar para os campos nas cláusulas ORDER BY, GROUP BY, onde são usados freqüentemente.

 

Exemplo 1:

 

SELECT vendas.*

FROM cliente c INNER JOIN vendas b

WHERE c.idade = 33

AND c.salario > 800

ORDER BY c.salario DESC

LIMIT 10;

Obs: Nesta consulta é recomendado ter índices para o campo salário.

 

Exemplo 2:

 

SELECT cliente.*

FROM cliente, ordem

WHERE cliente.id = ordem.id;

Obs: Tenha certeza que deverá ter índice para o cliente (id). Uniões sem índices são extremamente custosos.

 

Exemplo 3:

 

SELECT avg(idade)

FROM funcionarios

WHERE salario > 800;

Obs: Crie índice para idade e salario. Index (idade, salario).

 

Em busca do melhor índice

O melhor índice é aquele que seleciona melhor os campos a serem pesquisados. Quando ocorre uma consulta no banco de dados e ele pode percorrer mais registros do que o necessário para aquela pesquisa, contudo estes registros são desnecessários para nós programadores, isso representará um tempo perdido. Quanto menor o tempo perdido, maior performance. Portanto, o melhor índice será sempre aquele que apresentará uma maior seletividade as nossas consultas.