(Resolvido) Busca SQL de tabelas esta muito lenta e trava

[marcosfj1]

Bom gente seguinte tive que fazer uma busca bem complexa q envolvia mtas tabelas e muitas tabelas com muitos registros. Em ambiente de teste esta funcionando afinal o ambiente de teste o banco de dados é bem mais leve que o ambiente real.

quando vou executar no ambiente real a busca trava, acho q o servidor corta e entra em looping. então queria ajuda de vocês para tentar achar um solução que deixa a busca mais rapida. Por exemplo já ouvi falar de um recurso MySQL o LIMIT que você limita o numero de requisicoes deixando a busca mais rapida e evitando q ela trave, porem no meu caso a minha busca eu jogo ela em um array para mostrar ao usuario os dados encontrados e trabalhar com eles, portanto a opção de LIMIT não seria viavel pois não mostraria todos os dados

alguém pode me dar alguma ideia de como melhorar a velocidade da minha busca e evitar que ela trave?

Segue o codigo dela:

if ($_POST["id_cliente_uf"]) {
      
$sql = "SELECT CU.uf, CU.id_cliente_uf, CU.nome AS nome_uf, CC.nome AS nome_cidade, CC.id_cliente_uf, CC.id_cliente_cidade, SUM(PE.vl_total) AS vl_total,
          PE.id_cliente_endereco, PE.id_pedido_status, CE.cidade, SUM(PE.id_pedido_status) AS qtde_pedido
                      FROM nome_tabela CU
                      INNER JOIN nome_tabela CC ON CC.id_cliente_uf = CU.id_cliente_uf
                      INNER JOIN nome_tabela CE ON CE.id_cliente_cidade = CC.id_cliente_cidade
                      
                      INNER JOIN nome_tabela PE ON PE.id_cliente_endereco = CE.id_cliente_endereco
                      INNER JOIN nome_tabela C ON PE.id_cliente = C.id_cliente
                      WHERE CU.id_cliente_uf = {$_POST["id_cliente_uf"]} 
                      AND PE.id_pedido_status = 'x'
                      GROUP BY CC.id_cliente_cidade ";

Bom nesse codigo eu mostro as cidades que já fizeram pedido na loja apos o usuario por $_POST ter esolhido o estado. Depois faço o ARRAY para mostrar os dados que eu quero

$req = mysql_query($sql) or die(mysql_error());
          while ($cp = mysql_fetch_array($req)) {

Bom agora caso o usuario não selecione o estado a tela de entrada é uma relacao que mostrar todos os estados com a quantidade de pedidos por cada um....Traduzindo na primeira busca mostra todos os pedidos que foram feitos em todas as cidades daquele estado escolhido, CIDADE e PEDIDO Já a proxima busca que vou colocar é a tela de entrada onde mostrar todos os estados com todos os pedidos ESTADO e PEDIDO....o codigo é:

else if(!$_POST["id_cliente_uf"]) { 
            
$sql2 = "SELECT CU.uf, CU.id_cliente_uf, CC.id_cliente_uf, CC.id_cliente_cidade, SUM(PE.vl_total) AS vl_total,PE.id_cliente_endereco, PE.id_pedido_status, SUM(PE.id_pedido_status) AS qtde_pedido
                      FROM nome_tabela  CU
                      INNER JOIN nome_tabela CC ON CC.id_cliente_uf = CU.id_cliente_uf
                      INNER JOIN nome_tabela  CE ON CE.id_cliente_cidade = CC.id_cliente_cidade
                      INNER JOIN nome_tabela PE ON PE.id_cliente_endereco = CE.id_cliente_endereco
                      INNER JOIN nome_tabela  C ON PE.id_cliente = C.id_cliente
                      WHERE PE.id_pedido_status = 3
                      GROUP BY CU.uf ";

E em baixo eu faço um ARRAY para mostrar os dados

$req2 = mysql_query($sql2) or die(mysql_error());
while ($cp2 = mysql_fetch_array($req2)) {

Lembrando esta funcionando o q eu quero, mas no ambiente real esta busca por ser muito complexa trava então gostaria de saber se existe algum recurso q posso deixar ela mais rapida, sei la algum tipo de REFRESH a cada tantas consultas

alguém sabe me orientar?

Valeu

[Denis Courcy]

Bom gente seguinte tive que fazer uma busca bem complexa q envolvia mtas tabelas e muitas tabelas com muitos registros. Em ambiente de teste esta funcionando afinal o ambiente de teste o banco de dados é bem mais leve que o ambiente real.

quando vou executar no ambiente real a busca trava, acho q o servidor corta e entra em looping. então queria ajuda de vocês para tentar achar um solução que deixa a busca mais rapida.

Creio que seu sistema não está travando. ele está "processando" lentamente por causa de TABLE SCAN.

Há falta de índices em sua consulta que estão dificultando a resposta rápida.

Por exemplo já ouvi falar de um recurso MySQL o LIMIT que você limita o numero de requisicoes deixando a busca mais rapida e evitando q ela trave, porem no meu caso a minha busca eu jogo ela em um array para mostrar ao usuario os dados encontrados e trabalhar com eles, portanto a opção de LIMIT não seria viavel pois não mostraria todos os dados

Você pode usar o limit para fazer diversas consultas (consultas parciais) que preencherão a página em grid e possibilitarão a "rolagem" destes dados.

Você deverá usar a cláusula SQL_CALC_FOUND_ROWS juntamente com LIMIT e posteriormente um select para obter FOUND_ROWS() que retornará a quantidade total de linhas daquela consulta.

SELECT CU.uf, CU.id_cliente_uf, CU.nome AS nome_uf, CC.nome AS nome_cidade, CC.id_cliente_uf, 
   CC.id_cliente_cidade, SUM(PE.vl_total) AS vl_total,
   PE.id_cliente_endereco, PE.id_pedido_status, CE.cidade, SUM(PE.id_pedido_status) AS qtde_pedido
FROM nome_tabela CU
INNER JOIN nome_tabela CC ON CC.id_cliente_uf = CU.id_cliente_uf
INNER JOIN nome_tabela CE ON CE.id_cliente_cidade = CC.id_cliente_cidade
INNER JOIN nome_tabela PE ON PE.id_cliente_endereco = CE.id_cliente_endereco
INNER JOIN nome_tabela C ON PE.id_cliente = C.id_cliente
WHERE CU.id_cliente_uf = {$_POST["id_cliente_uf"]}  AND PE.id_pedido_status = 'x'
GROUP BY CC.id_cliente_cidade ";

Em seu código acima verifique: 1) se há indice para CC.id_cliente_uf; 2) se há indice para CE.id_cliente_cidade; 3) se há indice para PE.id_cliente_endereco; 4) se há indice para C.id_cliente; 5) se há indice para CU.id_cliente_uf e PE.id_pedido_status; 6) se há indice para CC.id_cliente_cidade.

SELECT CU.uf, CU.id_cliente_uf, CC.id_cliente_uf, CC.id_cliente_cidade, 
   SUM(PE.vl_total) AS vl_total,PE.id_cliente_endereco, PE.id_pedido_status, 
   SUM(PE.id_pedido_status) AS qtde_pedido
FROM nome_tabela  CU
INNER JOIN nome_tabela CC ON CC.id_cliente_uf = CU.id_cliente_uf
INNER JOIN nome_tabela  CE ON CE.id_cliente_cidade = CC.id_cliente_cidade
INNER JOIN nome_tabela PE ON PE.id_cliente_endereco = CE.id_cliente_endereco
INNER JOIN nome_tabela  C ON PE.id_cliente = C.id_cliente
WHERE PE.id_pedido_status = 3
GROUP BY CU.uf ";

Se existem os indices anteriormente relacionados verifique se há este índice: 1)CU.uf. Abaixo um exemplo de como usar o LIMIT do jeito que mencionei acima:

-- Listando de dez em dez linhas
SELECT SQL_CALC_FOUND_ROWS * 
FROM TABELA
WHERE c=x 
LIMIT 10;
-- Acima será mostrado as primeiras 10 linhas (mas minha tabela possui 100 registros e esta consulta exibiria 60 sem o limit
SELECT FOUND_ROWS();
-- informa que a consulta acima possui 60 linhas
-- Sabendo disso agora posso controlar meu limit para exibir as próximas 10 linhas, assim:
SELECT  * 
FROM TABELA
WHERE c=x 
LIMIT 11,20;

[marcosfj1]

valeu cara vou dar um estudada e depois te retorno

Realmente cara notei falta de alguns indices nas tabelas.....me esclarece algo eu tenho q criar eles como INDEX la no phpadmin?

Valeu

Editado Dezembro 6, 2010 por marcosfj1

[marcosfj1]

valeu cara vou dar um estudada e depois te retorno

Realmente cara notei falta de alguns indices nas tabelas.....me esclarece algo eu tenho q criar eles como INDEX la no phpadmin?

Valeu

Tambem não entendi muito bem isso do SQL_CALC_FOUND_ROWS isso é uma função? eu devo chama-la no meu select?

HEHE não sei muito de sql podia explicar melhor?

valeu

[Denis Courcy]

Oi 'marcosfj1',

Antes de falar sobre SQL_CALC_FOUND_ROWS devemos entender uma afirmação:

"O retorno de um SELECT é sempre uma tabela"

Se é uma tabela, então possui um determinado número de linhas nela, certo?

A cláusula SQL_CALC_FOUND_ROWS, quando colocada em uma instrução SELECT, alimentará uma variável interna do MySQL com o número total de linhas retornado por aquele select dentro da condição estabelecida.

Lembra que em meu post anterior informei que a tabela original possuia 100 linhas e que minha consulta com aquele WHERE deveria retornar 60 linhas? Pois é, a claúsula SQL_CALC_FOUND_ROWS guardou a quantidade total de linhas retornadas mesmo eu pedindo para exibir somente 10.

O select seguinte SELECT FOUND_ROWS(); trouxe para mim o que eu queria, o número 60 que estava em uma variável interna do MySQL.

Agora eu posso controlar como as linhas serão exibidas informando a linha inicial e a linha final na cláusula LIMIT .

Isto é muito bom quando estou usando páginas de internet e preciso "rolar" minha consulta.

Entendeu agora?

[marcosfj1]

Oi 'marcosfj1',

Antes de falar sobre SQL_CALC_FOUND_ROWS devemos entender uma afirmação:

"O retorno de um SELECT é sempre uma tabela"

Se é uma tabela, então possui um determinado número de linhas nela, certo?

A cláusula SQL_CALC_FOUND_ROWS, quando colocada em uma instrução SELECT, alimentará uma variável interna do MySQL com o número total de linhas retornado por aquele select dentro da condição estabelecida.

Lembra que em meu post anterior informei que a tabela original possuia 100 linhas e que minha consulta com aquele WHERE deveria retornar 60 linhas? Pois é, a claúsula SQL_CALC_FOUND_ROWS guardou a quantidade total de linhas retornadas mesmo eu pedindo para exibir somente 10.

O select seguinte SELECT FOUND_ROWS(); trouxe para mim o que eu queria, o número 60 que estava em uma variável interna do MySQL.

Agora eu posso controlar como as linhas serão exibidas informando a linha inicial e a linha final na cláusula LIMIT .

Isto é muito bom quando estou usando páginas de internet e preciso "rolar" minha consulta.

Entendeu agora?

Hum acho q entendi...porem se eu estabelecer o limit baseado na informacao do SQL_CALC_FOUND_ROWS e as linhas da minhas tabelas receberem novos registros então ele vai recalcular e eu terei q alterar o select? Pois em uma tabela retornada pelo SELECT q eu não sei o numero de linhas eu não posso estabelecer o limit, e se o numero e linhas é uma variavel crescente então eu teria q alterar o limit sempre?

Valeu a ajuda

[Denis Courcy]

Hum acho q entendi...porem se eu estabelecer o limit baseado na informacao do SQL_CALC_FOUND_ROWS e as linhas da minhas tabelas receberem novos registros então ele vai recalcular e eu terei q alterar o select? Pois em uma tabela retornada pelo SELECT q eu não sei o numero de linhas eu não posso estabelecer o limit, e se o numero e linhas é uma variavel crescente então eu teria q alterar o limit sempre?

Eu não gosto disso mas vou te responder com uma pergunta cretina:

O que te impede de guardar o valor de SELECT FOUND_ROWS(); em uma variável?

Não importa que número apareça nela. sempre exibirá o número máximo de linhas retornado em sua pesquisa, certo? ;) lol

[marcosfj1]

Cara valeu mesmo pela explicacao hehe....foi quase um palestra hehe

Aqui o problema era que as tabelas não tinham indices, o programador anterior não colocou nem um indices nas tabelas que eu tava utilizando, ai ficou dificil demais. Porem já coloquei indices onde necessario eu parece que resolveu, senti uma melhora grande....vamos ver se de agora para frente o negocio mantem a velocidade, mas acho q ta td certo

Obrigado novamente

Abraço