MYSQL - Count Query Taking Time

Question

I am running the following query and it's taking more than 12 sec on my local system.

SELECT DISTINCT
    entity_id_0
FROM
    (SELECT DISTINCT
        c0_.entity_id AS entity_id_0,
            c0_.sku AS sku_1,
            c0_.visible_in_grid AS visible_in_grid_2,
            c0_.has_options AS has_options_3,
            c0_.required_options AS required_options_4,
            c0_.created_at AS created_at_5,
            c0_.updated_at AS updated_at_6,
            c0_.type_id AS type_id_7,
            c0_.entity_type_id AS entity_type_id_8,
            c0_.attribute_set_id AS attribute_set_id_9
    FROM
        catalog_product_entity c0_
    INNER JOIN catalog_product_website c1_ ON c0_.entity_id = c1_.product_id
        AND (c1_.website_id = 1)
    INNER JOIN catalog_product_entity_varchar c2_ ON c0_.entity_id = c2_.entity_id
        AND (c2_.attribute_id = 71)
    WHERE
        (((c2_.value LIKE 'bed%')))
            AND c0_.type_id IN ('base' , 'simple', 'bundle', 'configurable')) dctrn_result

If I only run the sub Query it only takes 0.0027 sec

SELECT DISTINCT
        c0_.entity_id AS entity_id_0,
            c0_.sku AS sku_1,
            c0_.visible_in_grid AS visible_in_grid_2,
            c0_.has_options AS has_options_3,
            c0_.required_options AS required_options_4,
            c0_.created_at AS created_at_5,
            c0_.updated_at AS updated_at_6,
            c0_.type_id AS type_id_7,
            c0_.entity_type_id AS entity_type_id_8,
            c0_.attribute_set_id AS attribute_set_id_9
    FROM
        catalog_product_entity c0_
    INNER JOIN catalog_product_website c1_ ON c0_.entity_id = c1_.product_id
        AND (c1_.website_id = 1)
    INNER JOIN catalog_product_entity_varchar c2_ ON c0_.entity_id = c2_.entity_id
        AND (c2_.attribute_id = 71)
    WHERE
        (((c2_.value LIKE 'bed%')))
            AND c0_.type_id IN ('base' , 'simple', 'bundle', 'configurable')

When I tried to understand the query using explain the statement, It shows the following result, and I was not sure how I can improve the performance of the whole query.

Query with EXPLAIN

Answer 1

Do not use subquery which selects unneeded columns and results in additional execution step/level.

SELECT DISTINCT
        c0_.entity_id AS entity_id_0  
/*                                  ,
            c0_.sku AS sku_1,
            c0_.visible_in_grid AS visible_in_grid_2,
            c0_.has_options AS has_options_3,
            c0_.required_options AS required_options_4,
            c0_.created_at AS created_at_5,
            c0_.updated_at AS updated_at_6,
            c0_.type_id AS type_id_7,
            c0_.entity_type_id AS entity_type_id_8,
            c0_.attribute_set_id AS attribute_set_id_9
*/
    FROM
        catalog_product_entity c0_
    INNER JOIN catalog_product_website c1_ ON c0_.entity_id = c1_.product_id
        AND (c1_.website_id = 1)
    INNER JOIN catalog_product_entity_varchar c2_ ON c0_.entity_id = c2_.entity_id
        AND (c2_.attribute_id = 71)
    WHERE
        (((c2_.value LIKE 'bed%')))
            AND c0_.type_id IN ('base' , 'simple', 'bundle', 'configurable')

Answer 2

This smells like an EAV schema. Such designs are notorious for having poor performance when there are lots of rows.

These indexes should help some:

c2_:  INDEX(attribute_id, value, entity_id)
c0_:  INDEX(entity_type, type_id)
c1_:  INDEX(product_id, website_id)
c1_:  INDEX(website_id, product_id)

If value is a TEXT column the above index won't be allowed. See if you can change it to some civilized-size VARCHAR. If you are stuck with TEXT, then, try this:

c2_:  INDEX(attribute_id, value(20))

For further discussion, please provide SHOW CREATE TABLE for the relevant tables; there may be more techniques to help.