CODEWITHSUNDEEP
mysqlsqlperformance
Tom Smykowski
Tom Smykowski

Reputation: 26089

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

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?

Upvotes: 0

Views: 2024

Answers (3)

Gokul N K
Gokul N K

Reputation: 2458

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

Upvotes: 2

Zimbabao
Zimbabao

Reputation: 8240

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

Upvotes: 0

RichardTheKiwi
RichardTheKiwi

Reputation: 107706

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

Upvotes: 0

Related Questions