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

[landerbadi 0]

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?

 

 

[Jack Oliveira 1]

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 0]

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 1]

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 1]

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