Optimize SQL Query on three tables

Question

Optimize SQL Query on three tables

Navigation

#1 by (2 votes)

1

I have a product table that has a foreign key that is the category and a table that stores the name of the product photos. I usually make two queries: one to retrieve the category name and some product fields and another to collect an image of the product. In the product query I have:

$consultaProdutos = $_db->select("SELECT c.categoria, p.idproduto,  p.nome, p.valor FROM categoria AS c, produto as p WHERE p.ativo = 1 AND p.idcategoria = c.idcategoria");

The other query I perform within the result of the first query being it:

foreach ($consultaProdutos as $key => $rowProduto) {
        $consultaFoto = $_db->select("SELECT * FROM foto_produto WHERE idproduto = :idproduto LIMIT 1", array(':idproduto' => $rowProduto->idproduto ));
        $fotoProduto = $consultaFoto[0];
//html aqui

Where in the $ photoProduct variable there is the query result for the photo. However I believe it is more efficient if there is only one query.

php mysql sql

asked by anonymous 26.12.2014 / 22:49

1 answer

Problems with nfe's WSDL import Invisible RadioButton with spinner

score 2 · Accepted Answer

It's in your hand:

SELECT
    c.categoria,
    p.idproduto,
    p.nome,
    p.valor,
    fp.*
FROM
    categoria AS c,
    produto AS p,
    foto_produto AS fp
WHERE p.ativo = 1
AND p.idcategoria = c.idcategoria
AND fp.idproduto = p.idproduto

Do not forget to replace fp.* with the columns you'll really need in order to increase query performance.

In this case you will have several rows with the same value as the tables categoria and produto , but with different values for the foto_produto table.

Another idea is to group the values of the categoria and produto tables and use the GROUP_CONCAT function to return the photos in a single string .