Optimizing MySQL SELECT query with subqueries

Question

Hi is it possible to optimize the SELECT query below? The query itself is working but when we are querying large data we are encountering a message in php which is "Maximum execution time of 30 seconds exceeded". I reduced the columns in the query up to Topping3 but I am querying up to Topping15 column.

SELECT 
    itemID,
    itemName,
    Topping1,
    (SELECT DISTINCT Description FROM items WHERE PLU = a.Topping1 AND ClientID = 1679) AS Top1_desc,
    Topping2,
    (SELECT DISTINCT Description FROM items WHERE PLU = a.Topping2 AND ClientID = 1679) AS Top2_desc,
    Topping3,
    (SELECT DISTINCT Description FROM items WHERE PLU = a.Topping2 AND ClientID = 1679) AS Top3_desc,
FROM
    items a
WHERE
    ...

Current data on items table

--------------------------------------------------------------------
| itemID | itemName | Description | Topping1 | Topping2 | Topping3 |
--------------------------------------------------------------------
|    1   |   HAM1   |  Hamburger  |   ONI1   |   TOMO1  |          |
--------------------------------------------------------------------
|    2   |   ONI1   |    Onion    |          |          |          |
--------------------------------------------------------------------
|    3   |   TOMO1  |   Tomato    |          |          |          |
--------------------------------------------------------------------

and this is the expected result

--------------------------------------------------------------------------------------------------------
| itemID | itemName | Description | Topping1 | Top1_desc | Topping2 | Top2_desc | Topping3 | Top3_desc |
--------------------------------------------------------------------------------------------------------
|    1   |   HAM1   |  Hamburger  |   ONI1   |   Onion   |   TOMO1  |  Tomato   |          |          |
--------------------------------------------------------------------------------------------------------
|    2   |   ONI1   |    Onion    |          |           |          |           |          |          |
--------------------------------------------------------------------------------------------------------
|    3   |   TOMO1  |   Tomato    |          |           |          |           |          |          |
--------------------------------------------------------------------------------------------------------

Tum · Accepted Answer · 2020-04-03 09:45:23Z

1

This should be fast, unless there are a lot of entries with the same client ID. You could add LIMIT 1 after all subqueries, i.e.: (SELECT DISTINCT Description FROM items WHERE PLU = a.Topping1 AND ClientID = 1679 LIMIT 1) etc.

I suspect however that it is an index problem. Are the fields ClientID and PLU indexed?

EDIT: Alternative for your query:

SELECT 
    itemID,
    itemName,
    Topping1,
    t1.Description AS Top1_desc,
    Topping2,
    t2.Description AS Top2_desc,
    Topping3,
    t3.Description AS Top3_desc,
FROM
    items a
LEFT JOIN
    items t1 ON t1.PLU=a.Topping1 AND t1.ClientID = 1679
LEFT JOIN
    items t2 ON t2.PLU=a.Topping2 AND t2.ClientID = 1679
LEFT JOIN
    items t3 ON t3.PLU=a.Topping3 AND t3.ClientID = 1679
WHERE
    ...
GROUP BY
    a.itemID

Fields itemID, PLU and ClientID need indexes.

edited Apr 3, 2020 at 9:45

answered Apr 2, 2020 at 12:06

Tum

7,6852 gold badges27 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

jd_py Over a year ago

Hi, yes both the ClientID and PLU are indexed. We'll try to add LIMIT 1 to the subqueries.

Tum Over a year ago

As I don't feel the LIMIT will help I added an additional way to write your query. Joins are usually faster than subqueries.

Collectives™ on Stack Overflow

Optimizing MySQL SELECT query with subqueries

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related