title: Nested query is so slow in MySQL. How to optimize it?
body: <p>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).</p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>And i wrote this query for this:</p>
  
  <pre><code>SELECT p.NAME FROM hall_products as p where p.QUANTITY &gt;
   (SELECT MAX_QUANTITY from delivery_address WHERE CITY_CODE = p.CITY_CODE )
  </code></pre>
  
  <p>But this query is sooo slow.</p>
  
  <p>How to make it faster without database schema change aka only with change of query?</p>