Publicado em 14 de jul.

Código pho não executa em banco de dados grande

Tenho uma tabela chamada "item" com os seguintes campos: id, name, active. Nela tem cadastrado vários itens.

No campo "active" eu coloco a letra "S" para informar que este item está ativo no sistema. Por exemplo:

1, casa, S
2, mesa, S
3, cama, S
4, moto S
5, rádio

O quinto registro "radio" não está ativo no sistema pois não tem um "S" no campo active.

E outra tabela chamada "product" com os seguintes campos (id, name) com os seguintes registros:
1, Produto A
2, Produto B
3, Produto C

E uma terceira tabela chamada "product_item" com os seguintes campos (productID, itemID). No campo productID eu coloco o id de um produto da tabela "product" e no campo "itemID" eu coloco o id do produto da tabela "item". Exemplo:

1, 1
1, 3
1, 4
2, 3
2, 4
Sendo assim o produto A da tabela 'product" comtem os itens casa, cama e moto.

Eu preciso fazer uma busca da seguinte maneira:

Eu escolho um registro da tabela "item", por exemplo "casa". Preciso fazer com que o php me liste todos os registros da tabela "product" que contenham a palavra "casa" e que os demais itens estejam ativos no siste. Ou seja, que contenham um "S" no campo "active"

Eu consegui fazer isso da seguinte maneira:

SELECT P.id, P.name, GROUP_CONCAT(I.name ORDER BY I.name) AS items
FROM product P
JOIN product_item PI ON P.id = PI.productID
JOIN item I ON I.id = PI.itemID AND I.active = 'S'
WHERE P.id NOT IN (

 SELECT PI.productID
 FROM product_item PI
 JOIN item I ON I.id = PI.itemID
 WHERE I.active IS NULL
)

AND P.id IN (

 SELECT PI.productID
 FROM product_item PI
 JOIN item I ON I.id = PI.itemID
 WHERE I.name = 'mesa'
)
GROUP BY P.id, P.name;

	O problema que eu estou tendo é o seguinte:

	Quando eu jogo este código para o banco de dados onde eu já tenho os registros cadastrado o php fica lendo uma eternidade e não lista os produtos.

	 

	Usando código no banco de dados que eu fiz para testes ele funciona perfeitamente pois nele tem poucos registros.

	 

	No banco de dados principal a tabela "item" tem 11.196 registros. A tabela "product" tem 88.214 registros e a tabela "product_item" tem 518.378 registros. 

	 

	Eu acredito que, devido o banco de dados ser muito grande, ele não consegue listar.

	 

	Alguém sabe de algum meio de resolver isso?

Discussão (4)

Entre ou cadastre-se para participar da discussão

Entrar Criar conta

Jack Oliveira· 14 de jul.

Reescreva sua consulta para minimizar o número de operações e junções complexas. Vou fornecer uma versão otimizada da sua consulta:

SELECT
 P.id,
 P.name,
 GROUP_CONCAT(I.name ORDER BY I.name) AS items

FROM product P JOIN product_item PI ON P.id = PI.productID JOIN item I ON I.id = PI.itemID WHERE P.id IN (

 SELECT PI.productID
 FROM product_item PI
 JOIN item I ON I.id = PI.itemID
 WHERE I.name = 'casa'
 )
 AND P.id NOT IN (
 SELECT PI.productID
 FROM product_item PI
 JOIN item I ON I.id = PI.itemID
 WHERE I.active IS NULL OR I.active != 'S'
 )

GROUP BY

 P.id, P.name;

	utilização de subconsultas pode ser custosa, especialmente em grandes bancos de dados. Tente minimizar subconsultas complexas. Vou tentar reescrever a consulta para evitar subconsultas:

	 

SELECT
 P.id,
 P.name,
 GROUP_CONCAT(I.name ORDER BY I.name) AS items

FROM product P JOIN product_item PI1 ON P.id = PI1.productID JOIN item I1 ON I1.id = PI1.itemID WHERE I1.name = 'casa' AND NOT EXISTS (

 SELECT 1
 FROM product_item PI2
 JOIN item I2 ON I2.id = PI2.itemID
 WHERE
 PI2.productID = P.id
 AND (I2.active IS NULL OR I2.active != 'S')
 )

GROUP BY

 P.id, P.name;

	Execute comandos de análise de índices para garantir que o banco de dados está utilizando os índices de forma eficiente:

	 

ANALYZE TABLE item;
ANALYZE TABLE product;
ANALYZE TABLE product_item;

	A principal ideia é reduzir a complexidade da consulta e garantir que os índices adequados estão sendo utilizados pelo banco de dados. Além disso, certifique-se de testar cada otimização individualmente para verificar se há melhorias no desempenho.

landerbadi· 15 de jul.

19 horas atrás, Jack Oliveira disse:

Reescreva sua consulta para minimizar o número de operações e junções complexas. Vou fornecer uma versão otimizada da sua consulta:

SELECT
 P.id,
 P.name,
 GROUP_CONCAT(I.name ORDER BY I.name) AS items

FROM product P JOIN product_item PI ON P.id = PI.productID JOIN item I ON I.id = PI.itemID WHERE P.id IN (

 SELECT PI.productID
 FROM product_item PI
 JOIN item I ON I.id = PI.itemID
 WHERE I.name = 'casa'
 )
 AND P.id NOT IN (
 SELECT PI.productID
 FROM product_item PI
 JOIN item I ON I.id = PI.itemID
 WHERE I.active IS NULL OR I.active != 'S'
 )

GROUP BY

 P.id, P.name;

 utilização de subconsultas pode ser custosa, especialmente em grandes bancos de dados. Tente minimizar subconsultas complexas. Vou tentar reescrever a consulta para evitar subconsultas:

  

SELECT
 P.id,
 P.name,
 GROUP_CONCAT(I.name ORDER BY I.name) AS items

FROM product P JOIN product_item PI1 ON P.id = PI1.productID JOIN item I1 ON I1.id = PI1.itemID WHERE I1.name = 'casa' AND NOT EXISTS (

 SELECT 1
 FROM product_item PI2
 JOIN item I2 ON I2.id = PI2.itemID
 WHERE
 PI2.productID = P.id
 AND (I2.active IS NULL OR I2.active != 'S')
 )

GROUP BY

 P.id, P.name;

 Execute comandos de análise de índices para garantir que o banco de dados está utilizando os índices de forma eficiente:

  

ANALYZE TABLE item;
ANALYZE TABLE product;
ANALYZE TABLE product_item;

 A principal ideia é reduzir a complexidade da consulta e garantir que os índices adequados estão sendo utilizados pelo banco de dados. Além disso, certifique-se de testar cada otimização individualmente para verificar se há melhorias no desempenho.

	Muito obrigado. Testei aqui e funcionou. Demora cerca de 30 segundos para fazer a listagem mais isso não incomoda.

	 

	Estou com uma outra dúvida. Nesta parte do código:

SELECT PI.productID
 FROM product_item PI
 JOIN item I ON I.id = PI.itemID
 WHERE I.name = 'casa'

	Ele busca pela palavra "casa". Tem como fazer com que ele liste também um outro item? Por exemplo: Listar todos os registros que contenham o item "casa" e também os registros que contenham o item "apartamento"?

Jack Oliveira· 15 de jul.

Se os nomes dos itens forem exatamente "casa" e "apartamento" pode usar IN

SELECT PI.productID
FROM product_item PI
JOIN item I ON I.id = PI.itemID
WHERE I.name IN ('casa', 'apartamento')

Com php

<?php
// Configuração da conexão

$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database";

// Cria a conexão
$conn = new mysqli($servername, $username, $password, $dbname);

// Verifica a conexão

if ($conn->connect_error) {
 die("Falha na conexão: " . $conn->connect_error);
}

// Define os itens que você deseja buscar
$items = ['casa', 'apartamento'];

// Cria a consulta

$sql = "SELECT PI.productID
 FROM product_item PI
 JOIN item I ON I.id = PI.itemID
 WHERE I.name IN ('" . implode("','", array_map([$conn, 'real_escape_string'], $items)) . "')";

// Executa a consulta

$result = $conn->query($sql);

if ($result->num_rows > 0) {
 // Saída dos dados de cada linha
 while ($row = $result->fetch_assoc()) {
 echo "productID: " . $row["productID"]. "<br>";
 }

} else {

 echo "0 resultados";
}

// Fecha a conexão

$conn->close();
?>

	 

	Se você precisar buscar por correspondências parciais, como "casa%" ou "apartamento%":

	 

	Pode usar OR com Like

	 

SELECT PI.productID

FROM product_item PI
JOIN item I ON I.id = PI.itemID
WHERE I.name LIKE 'casa%' OR I.name LIKE 'apartamento%'

Php

<?php
// Configuração da conexão

$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database";

// Cria a conexão
$conn = new mysqli($servername, $username, $password, $dbname);

// Verifica a conexão

if ($conn->connect_error) {
 die("Falha na conexão: " . $conn->connect_error);
}

// Define os itens que você deseja buscar
$items = ['casa', 'apartamento'];

// Cria a consulta

$sql = "SELECT PI.productID
 FROM product_item PI
 JOIN item I ON I.id = PI.itemID
 WHERE ";
$whereClauses = [];
foreach ($items as $item) {
 $escapedItem = $conn->real_escape_string($item);
 $whereClauses[] = "I.name LIKE '%$escapedItem%'";
}
$sql .= implode(' OR ', $whereClauses);

// Executa a consulta

$result = $conn->query($sql);

if ($result->num_rows > 0) {
 // Saída dos dados de cada linha
 while ($row = $result->fetch_assoc()) {
 echo "productID: " . $row["productID"]. "<br>";
 }

} else {

 echo "0 resultados";
}

// Fecha a conexão

$conn->close();
?>

	 

	Agora se você estiver buscando pelos nomes exatos e preferir a sintaxe com OR

	 

SELECT PI.productID

FROM product_item PI
JOIN item I ON I.id = PI.itemID

WHERE I.name = 'casa' OR I.name = 'apartamento'

<?php

// Configuração da conexão

$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database";

// Cria a conexão
$conn = new mysqli($servername, $username, $password, $dbname);

// Verifica a conexão

if ($conn->connect_error) {
 die("Falha na conexão: " . $conn->connect_error);
}

// Define os itens que você deseja buscar
$items = ['casa', 'apartamento'];

// Cria a consulta

$sql = "SELECT PI.productID
 FROM product_item PI
 JOIN item I ON I.id = PI.itemID
 WHERE ";
$whereClauses = [];
foreach ($items as $item) {
 $escapedItem = $conn->real_escape_string($item);
 $whereClauses[] = "I.name = '$escapedItem'";
}
$sql .= implode(' OR ', $whereClauses);

// Executa a consulta

$result = $conn->query($sql);

if ($result->num_rows > 0) {
 // Saída dos dados de cada linha
 while ($row = $result->fetch_assoc()) {
 echo "productID: " . $row["productID"]. "<br>";
 }

} else {

 echo "0 resultados";
}

// Fecha a conexão

$conn->close();
?>

	 

	 

	Se você precisar de correspondências exatas, utilize IN ou OR com igualdade. Se precisar de correspondências parciais, utilize LIKE com OR.

	 

	 

	O php é só uma base de como poderia esta fazendo blz..

	 

	Ajuste a sua maneira do seu projeto

Jack Oliveira· 15 de jul.

1 hora atrás, landerbadi disse:

Muito obrigado. Testei aqui e funcionou. Demora cerca de 30 segundos para fazer a listagem mais isso não incomoda

otimizar sua consulta e melhorar a performance, a ideia principal é evitar subconsultas complexas e garantir que os índices corretos estão sendo utilizados.

SELECT
 P.id,
 P.name,
 GROUP_CONCAT(I1.name ORDER BY I1.name) AS items

FROM product P JOIN product_item PI1 ON P.id = PI1.productID JOIN item I1 ON I1.id = PI1.itemID LEFT JOIN

 (SELECT PI2.productID
 FROM product_item PI2
 JOIN item I2 ON I2.id = PI2.itemID
 WHERE I2.active IS NULL OR I2.active != 'S'
 GROUP BY PI2.productID) AS InactiveItems ON P.id = InactiveItems.productID

WHERE I1.name = 'casa' AND InactiveItems.productID IS NULL GROUP BY

 P.id, P.name;

	os índices apropriados estão configurados para as tabelas envolvidas. Os seguintes índices são recomendados:

	 

CREATE INDEX idx_product_item_productID ON product_item(productID);
CREATE INDEX idx_product_item_itemID ON product_item(itemID);
CREATE INDEX idx_item_id ON item(id);
CREATE INDEX idx_item_name ON item(name);
CREATE INDEX idx_item_active ON item(active);

	Php

	 

<?php

// Configuração da conexão

$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database";

// Cria a conexão
$conn = new mysqli($servername, $username, $password, $dbname);

// Verifica a conexão

if ($conn->connect_error) {
 die("Falha na conexão: " . $conn->connect_error);
}

// Cria a consulta
$sql = "

SELECT
 P.id,
 P.name,
 GROUP_CONCAT(I1.name ORDER BY I1.name) AS items

FROM
product P
JOIN
product_item PI1 ON P.id = PI1.productID
JOIN
item I1 ON I1.id = PI1.itemID
LEFT JOIN

 (SELECT PI2.productID
 FROM product_item PI2
 JOIN item I2 ON I2.id = PI2.itemID
 WHERE I2.active IS NULL OR I2.active != 'S'
 GROUP BY PI2.productID) AS InactiveItems ON P.id = InactiveItems.productID

WHERE
I1.name = 'casa'
AND InactiveItems.productID IS NULL
GROUP BY

 P.id, P.name;
";

// Executa a consulta

$result = $conn->query($sql);

if ($result->num_rows > 0) {
 // Saída dos dados de cada linha
 while ($row = $result->fetch_assoc()) {
 echo "ID: " . $row["id"] . " - Nome: " . $row["name"] . " - Itens: " . $row["items"] . "<br>";
 }

} else {

 echo "0 resultados";
}

// Fecha a conexão

$conn->close();
?>

	Veja se sql assim ajudar melhor o tempo da consulta