[Solved] Is my MySQL query OK or can it be improved?
Looking to automatically optimize YOUR SQL query? Start for free.

EverSQL Database Performance Knowledge Base

Is my MySQL query OK or can it be improved?

Database type:

I wrote a query to select products from MySQL:

Here's what it looks like:

SELECT `products`.`id` AS `id`, `products`.`name` AS `name`, `products`.`slug` AS `slug`, `products`.`ord` AS `ord`, 
SUBSTRING_INDEX(GROUP_CONCAT(products_photos.name ORDER BY products_photos.ord ASC SEPARATOR ","), ",", 1) AS `photo`, 
SUBSTRING_INDEX(GROUP_CONCAT(products_attributes.price ORDER BY products_attributes.price ASC SEPARATOR ","), ",", 1) AS `price` 

FROM `products` 

LEFT JOIN `products_photos` ON `products`.`id` = `products_photos`.`product_id` 
LEFT JOIN `products_attributes` ON `products`.`id` = `products_attributes`.`product_id` 
LEFT JOIN `products_categories` ON `products`.`id` = `products_categories`.`product_id` 

WHERE `products_categories`.`category_id` = '5' AND `products`.`status` = '1' 

GROUP BY `products`.`id` 

ORDER BY `ord` ASC

I'm wondering about photos and attributes part of query, is there a better way to select first photo and smallest price from joined tables? There will be a lot of products in my table and I'm worried about future performance as I'm not very good at MySQL :)

Any pointers would be appreciated :)

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Prefer Inner Join Over Left Join (modified query below): We identified that one or more left joined entities (e.g. `products_categories`) are used in the 'where' clause, in a way that allows to replace it with an optimized inner join. Inner joins can be fully optimized by the database, while Left joins apply limitations on the database's optimizer.
  2. Use Numeric Column Types For Numeric Values (query line: 28): Referencing a numeric value (e.g. 5) as a string in a WHERE clause might result in poor performance. Possible impacts of storing numbers as varchars: more space will be used, you won't be able to perform arithmetic operations, the data won't be self-validated, aggregation functions like SUM won't work, the output may sort incorrectly and more. If the column is numeric, remove the quotes from the constant value, to make sure a numeric comparison is done.
  3. Use Numeric Column Types For Numeric Values (query line: 29): Referencing a numeric value (e.g. 1) as a string in a WHERE clause might result in poor performance. Possible impacts of storing numbers as varchars: more space will be used, you won't be able to perform arithmetic operations, the data won't be self-validated, aggregation functions like SUM won't work, the output may sort incorrectly and more. If the column is numeric, remove the quotes from the constant value, to make sure a numeric comparison is done.
The optimized query:
SELECT
        `products`.`id` AS `id`,
        `products`.`name` AS `name`,
        `products`.`slug` AS `slug`,
        `products`.`ord` AS `ord`,
        SUBSTRING_INDEX(GROUP_CONCAT(products_photos.name 
    ORDER BY
        products_photos.ord ASC SEPARATOR ','),
        ",",
        1) AS `photo`,
        SUBSTRING_INDEX(GROUP_CONCAT(products_attributes.price 
    ORDER BY
        products_attributes.price ASC SEPARATOR ','),
        ",",
        1) AS `price` 
    FROM
        `products` 
    LEFT JOIN
        `products_photos` 
            ON `products`.`id` = `products_photos`.`product_id` 
    LEFT JOIN
        `products_attributes` 
            ON `products`.`id` = `products_attributes`.`product_id` 
    INNER JOIN
        `products_categories` 
            ON `products`.`id` = `products_categories`.`product_id` 
    WHERE
        `products_categories`.`category_id` = '5' 
        AND `products`.`status` = '1' 
    GROUP BY
        `products`.`id` 
    ORDER BY
        `products`.`ord` ASC

Related Articles



* original question posted on StackOverflow here.