Nested query is so slow in MySQL. How to optimize it?

Question

I have a table hall_products with products. Each product has a NAME, is sold in specified QUANTITY and only to one city pointed by CITY_CODE. Now next table is delivery_address. Every delivery address is a city, it contains a CITY_CODE and MAX_QUANTITY that is a maximum quantity of a product that can be sent to this city (no matter what product).

So for example I have a product Milk with quantity 1 liter and addressed to the city with code 14 that means Berlin. And maximum quantity of good i can send to Berlin (city code 14) is 0,7 liter. Than i get ship the Milk to Berlin because quantity is higher than max_quantity.

What i want to get is names of all goods that can be shipped anywhere to the world. So i need to get all goods that fit into max_quantity limit of the target city.

And i wrote this query for this:

SELECT p.NAME FROM hall_products as p where p.QUANTITY >
 (SELECT MAX_QUANTITY from delivery_address WHERE CITY_CODE = p.CITY_CODE )

But this query is sooo slow.

How to make it faster without database schema change aka only with change of query?

Is that the query you actually use? It seems like a weird query to me.. the double from in the nested query and the * make it seem like it wouldn't work that well... — gnur
– gnur, Commented Mar 3, 2011 at 11:35
The query will not run at all. It is not allowed to return more than one column in the sub-select when using the > operator against it (actually I think it is never possible in MySQL to return more than one column in a subselect in MySQL) — user330315
– user330315, Commented Mar 3, 2011 at 11:40
Not only the query will not run, the example is also wrong: product Milk with quantity 1 liter ... And maximum quantity of good i can send to Berlin (city code 14) is 0,7 liter. Than i get ship the Milk to Berlin because quantity is lower than max_quantity I'm voting down. — Clodoaldo Neto
– Clodoaldo Neto, Commented Mar 3, 2011 at 11:54
@gnur, @a_horse_with_no_name - there was a mistake in query, now it's ok. — Tom Smykowski
– Tom Smykowski, Commented Mar 4, 2011 at 7:49

Clodoaldo Neto · Accepted Answer · 2011-03-03 12:09:36Z

2

In general scenarios joins are considered to be better than nested queries. But there are scenarios where a nested query may perform better than joins.

Try this query and compare the results.

SELECT p.NAME 
FROM hall_products as p, delivery_address as d 
where p.QUANTITY < d.MAX_QUANTITY and d.CITY_CODE = p.CITY_CODE

edited Mar 3, 2011 at 12:09

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

answered Mar 3, 2011 at 11:46

Gokul N K

2,4582 gold badges33 silver badges41 bronze badges

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

Comments

RichardTheKiwi · Accepted Answer · 2011-03-03 11:47:17Z

0

So i need to get all goods that fit into max_quantity limit of the target city.

Fit into means <= in my books, but I'm not on maths.se, so maybe you know something I don't.

A sub-query may be run for each-row of the result, it is normally slower than creating a JOIN. Here's a JOIN in ANSI syntax

SELECT p.NAME
FROM hall_products p
JOIN delivery_address a on a.CITY_CODE = p.CITY_CODE
where p.QUANTITY <= a.MAX_QUANTITY

answered Mar 3, 2011 at 11:47

RichardTheKiwi

108k28 gold badges206 silver badges269 bronze badges

Comments

Clodoaldo Neto · Accepted Answer · 2011-03-03 12:07:49Z

0

Try

SELECT p.NAME 
FROM hall_products as p, delivery_address as d 
where p.QUANTITY > d.MAX_QUANTITY and d.CITY_CODE = p.CITY_CODE

This will work fine again if you have indexes on QUANTITY and CITY_CODE

edited Mar 3, 2011 at 12:07

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

answered Mar 3, 2011 at 11:37

Zimbabao

8,2803 gold badges32 silver badges36 bronze badges

Collectives™ on Stack Overflow

Nested query is so slow in MySQL. How to optimize it?

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related