How to have Mysql query (with multiple join) faster and more efficient

Question

I have a big problem with the execution of a MySql query that is very slow.. too slow... unusable!

To read 1000 products with their prices it takes more than 20 seconds!!!

$dati = mysqli_query($mysqli_connect, "
SELECT      *  
FROM        $tb_products
LEFT JOIN   $tb_categories ON $tb_products.product_category = $tb_categories.category_id_master
LEFT JOIN   $tb_subcategories ON $tb_products.product_subcategory = $tb_subcategories.subcategory_id_master
LEFT JOIN   $tb_logos ON $tb_products.product_logo = $tb_logos.logo_id_master

LEFT JOIN   $tb_prices ON ( 
            $tb_products.product_brand = $tb_prices.price_brand
            AND $tb_products.product_code = $tb_prices.price_code
            AND $tb_prices.price_validity = (
                SELECT  MAX($tb_prices.price_validity) 
                FROM    $tb_prices 
                WHERE   $tb_prices.price_validity<=DATE_ADD(CURDATE(), INTERVAL +0 DAY)
                        AND $tb_products.product_code = $tb_prices.price_code
            )
        )

WHERE       $tb_products.product_language='$activeLanguage' AND $tb_products.product_category!=0
GROUP BY    $tb_products.product_code
ORDER BY    $tb_products.product_brand, $tb_categories.category_rank, $tb_subcategories.subcategory_rank, $tb_products.product_subcategory, $tb_products.product_rank
");

EDIT:
I've changed, as suggested from Mr.Alvaro, the SELECT * with a more efficient SELECT [list of values] and the execution time dropped from 20 seconds to 14 seconds. Still too slow...
END EDIT

Each product can have different prices, so I use the (select max...) to take the most recent (but not future) price. Maybe is this function that slow down everything? Are there better solutions in your opinion?

Consider that the same query without the join with the prices it takes only 0.2 seconds. So I'm convinced that the problem is all in that part of the code.

$dati = mysqli_query($mysqli_connect, "
SELECT      *  
FROM        $tb_products
LEFT JOIN   $tb_categories ON $tb_products.product_category = $tb_categories.category_id_master
LEFT JOIN   $tb_subcategories ON $tb_products.product_subcategory = $tb_subcategories.subcategory_id_master
LEFT JOIN   $tb_logos ON $tb_products.product_logo = $tb_logos.logo_id_master

WHERE       $tb_products.product_language='$activeLanguage' AND $tb_products.product_category!=0
GROUP BY    $tb_products.product_code
ORDER BY    $tb_products.product_brand, $tb_categories.category_rank, $tb_subcategories.subcategory_rank, $tb_products.product_subcategory, $tb_products.product_rank
");

I also considered the fact that it could depend on the power of the server but I would tend to exclude it because the second query (without prices) is quite acceptable as speed.

The prices table is as following

+----------------+-------------+
| price_id       | int(3)      |
| price_brand    | varchar(5)  |
| price_code     | varchar(50) |
| price_value    | float(10,2) |
| price_validity | date        |
| price_language | varchar(2)  |
+----------------+-------------+

Answer 1

Maybe is because you are using SELECT *, this is known as bad practice. Check this question in stack overflow.

Is there a difference between Select * and Select [list each col]

There, Mitch Wheat wrote:

You should specify an explicit column list. SELECT * will bring back more columns than you need creating more IO and network traffic, but more importantly it might require extra lookups even though a non-clustered covering index exists (On SQL Server). Blockquote

Answer 2

SOLVED

The problem was in the last JOIN with the prices table. Following the suggestions I managed to execute the SELECT MAX (...) separately and it tooks 0.1 seconds to execute.

So I decide to run the main query without the prices and then, in the WHILE cicle to fetch the array, I run a second query to take the price for every single product! This work perfectly and my page has dropped down from 20 seconds to a few tenths of a second.

So, the code become something like this:

$dati = mysqli_query($mysqli_connect, "
SELECT      *  
FROM        $tb_products
LEFT JOIN   $tb_categories ON $tb_products.product_category =     $tb_categories.category_id_master
LEFT JOIN   $tb_subcategories ON $tb_products.product_subcategory =     $tb_subcategories.subcategory_id_master
LEFT JOIN   $tb_logos ON $tb_products.product_logo = $tb_logos.logo_id_master

WHERE       $tb_products.product_language='$activeLanguage' AND     $tb_products.product_category!=0
GROUP BY    $tb_products.product_code
ORDER BY    $tb_products.product_brand, $tb_categories.category_rank,     $tb_subcategories.subcategory_rank, $tb_products.product_subcategory,     $tb_products.product_rank
");

and then..

while ($array = mysqli_fetch_array($dati)) {

    $code = $array['product_code'];

    $dati_prices = mysqli_query($mysqli_connect, "
    SELECT  * 
    FROM    $tb_prices 
    WHERE   $tb_prices.price_brand = '$brand' AND $tb_prices.price_code = '$code' AND $tb_prices.price_validity = (
            SELECT  MAX($tb_prices.price_validity) 
            FROM    $tb_prices 
            WHERE   $tb_prices.price_validity<=DATE_ADD(CURDATE(), INTERVAL +0 DAY) AND $tb_prices.price_code = '$code'
    )
    GROUP BY    $tb_prices.price_code           
    ") ;

}

Probably is not the best and elegant solution but for me works pretty well!