[Solved] Slow MySQL query with subquery from table

EverSQL Database Performance Knowledge Base

Slow MySQL query with subquery from table

Database type:

I am trying to bring back a string based on an IF statement but it is extremely slow.

It has something to do with the first subquery but I am unsure of how to rearrange this as to bring back the same results but faster.

Here is my SQL:

SELECT IF
(
    (
        SELECT COUNT(*)
        FROM 
            (
                SELECT DISTINCT enquiryId, type 
                FROM parts_enquiries, parts_service_types AS pst 
                WHERE parts_enquiries.serviceTypeId = pst.id
            ) AS parts
        WHERE parts.enquiryId = enquiries.id
    ) > 1, 'Mixed',
    (
        SELECT DISTINCT type 
        FROM parts_enquiries, parts_service_types AS pst 
        WHERE parts_enquiries.serviceTypeId = pst.id AND enquiryId = enquiries.id
    )
) AS partTypes
FROM  enquiries,
entities
WHERE enquiries.entityId = entities.id

How can I make it faster?

I have modified my original query below, but I am getting the error that subquery returns more than one row:

SELECT
(SELECT
   CASE WHEN COUNT(DISTINCT type) > 1 THEN 'Mixed' ELSE `type` END AS type
FROM parts_enquiries 
INNER JOIN parts_service_types AS pst ON parts_enquiries.serviceTypeId = pst.id
INNER JOIN enquiries ON parts_enquiries.enquiryId = enquiries.id
INNER JOIN entities ON enquiries.entityId = entities.id
GROUP BY enquiryId) AS partTypes
FROM  enquiries,
entities
WHERE enquiries.entityId = entities.id

EXPLAIN result

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 Correlated Subqueries (query line: 14): A correlated subquery is a subquery that contains a reference (column: id) to a table that also appears in the outer query. Usually correlated queries can be rewritten with a join clause, which is the best practice. The database optimizer handles joins much better than correlated subqueries. Therefore, rephrasing the query with a join will allow the optimizer to use the most efficient execution plan for the query.
  2. Avoid Subqueries (query line: 5): 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.
  3. 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.
Optimal indexes for this query:
ALTER TABLE `enquiries` ADD INDEX `enquiries_idx_entityid` (`entityId`);
ALTER TABLE `entities` ADD INDEX `entities_idx_id` (`id`);
ALTER TABLE `parts_enquiries` ADD INDEX `parts_enquiries_idx_servicetypeid` (`serviceTypeId`);
ALTER TABLE `parts_service_types` ADD INDEX `parts_types_idx_id` (`id`);
The optimized query:
SELECT
        IF((SELECT
            COUNT(*) 
        FROM
            (SELECT
                DISTINCT enquiryId,
                type 
            FROM
                parts_enquiries,
                parts_service_types AS pst 
            WHERE
                parts_enquiries.serviceTypeId = pst.id) AS parts 
        WHERE
            parts.enquiryId = enquiries.id) > 1, 'Mixed', (SELECT
                DISTINCT type 
            FROM
                parts_enquiries,
                parts_service_types AS pst 
            WHERE
                parts_enquiries.serviceTypeId = pst.id 
                AND enquiryId = enquiries.id)) AS partTypes 
        FROM
            enquiries,
            entities 
        WHERE
            enquiries.entityId = entities.id

Related Articles



* original question posted on StackOverflow here.