[Solved] Subquery LIMIT causing issues when WHERE outside of subquery implemented

EverSQL Database Performance Knowledge Base

Subquery LIMIT causing issues when WHERE outside of subquery implemented

The subquery limits results perfectly when the WHERE clause at the end of the statement is not included. I understand that the subquery LIMIT happens first then the WHERE clause is fired on that result set and that this is a limitation/restriction of a subquery.

What I need is someone more experienced than me to help a brother out with retrieving the records with a LIMIT with the ability to restrict that result set by the WHERE clauses. Let me know if this was not explained well enough.

I also scoured the interwebs in search of the answer for hours with no luck. Your time is appreciated.

EDIT: added a crude example on SQLfiddle: http://sqlfiddle.com/#!9/2de563/4

    SELECT *
    FROM (SELECT * FROM parent_products LIMIT 10) pp
    INNER JOIN products_variants pv ON pv.parent_id=pp.parent_id
    INNER JOIN products p ON p.id=pv.product_id
    INNER JOIN product_types pt ON pt.product_type_id=p.product_type
    LEFT JOIN team_list t ON pp.team_id=t.team_id
    LEFT JOIN photos ph ON ph.product_id=p.id
    LEFT JOIN product_attributes pa ON pa.product_id=pv.product_id
    LEFT JOIN attributes a ON a.id=pa.attribute_id
    LEFT JOIN product_attribute_options po ON po.product_attribute_option_id=a.parent_id
    WHERE t.team_id=100 AND p.active='y';

Explain select: enter image description here

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. Avoid Selecting Unnecessary Columns (query line: 2): Avoid selecting all columns with the '*' wildcard, unless you intend to use them all. Selecting redundant columns may result in unnecessary performance degradation.
  2. Avoid Selecting Unnecessary Columns (query line: 5): Avoid selecting all columns with the '*' wildcard, unless you intend to use them all. Selecting redundant columns may result in unnecessary performance degradation.
  3. Avoid Subqueries (query line: 4): We advise against using subqueries as they are not optimized well by the optimizer. Therefore, it's recommended to join a newly created temporary table that holds the data, which also includes the relevant search index.
  4. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
  5. Prefer Inner Join Over Left Join (modified query below): We identified that one or more left joined entities (e.g. `team_list`) 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.
Optimal indexes for this query:
ALTER TABLE `attributes` ADD INDEX `attributes_idx_id` (`id`);
ALTER TABLE `photos` ADD INDEX `photos_idx_product_id` (`product_id`);
ALTER TABLE `product_attribute_options` ADD INDEX `product_options_idx_product_id` (`product_attribute_option_id`);
ALTER TABLE `product_attributes` ADD INDEX `product_attributes_idx_product_id` (`product_id`);
ALTER TABLE `product_types` ADD INDEX `product_types_idx_product_id` (`product_type_id`);
ALTER TABLE `products` ADD INDEX `products_idx_active` (`active`);
ALTER TABLE `products_variants` ADD INDEX `products_variants_idx_product_id` (`product_id`);
ALTER TABLE `team_list` ADD INDEX `team_list_idx_team_id` (`team_id`);
The optimized query:
SELECT
        * 
    FROM
        (SELECT
            * 
        FROM
            parent_products LIMIT 10) pp 
    INNER JOIN
        products_variants pv 
            ON pv.parent_id = pp.parent_id 
    INNER JOIN
        products p 
            ON p.id = pv.product_id 
    INNER JOIN
        product_types pt 
            ON pt.product_type_id = p.product_type 
    INNER JOIN
        team_list t 
            ON pp.team_id = t.team_id 
    LEFT JOIN
        photos ph 
            ON ph.product_id = p.id 
    LEFT JOIN
        product_attributes pa 
            ON pa.product_id = pv.product_id 
    LEFT JOIN
        attributes a 
            ON a.id = pa.attribute_id 
    LEFT JOIN
        product_attribute_options po 
            ON po.product_attribute_option_id = a.parent_id 
    WHERE
        t.team_id = 100 
        AND p.active = 'y'

Related Articles



* original question posted on StackOverflow here.