2

I have a Companies table and a Zipcodes table and I want to get the samllest subset of companyIDs before I apply additional processing. So often I put that in an inner select, grabbing the companyIDs on some WHERE condition and then go some aggregation or calculation (like maybe a distance calculation) on the outside.

For some reason I'm not understanding why this outer SELECT is slow while the inner query is blazing fast.

I know it has to do with the JOIN on the inside. Because removing it fixes the problem. Somehow MySQL is deriving the tables... SELECT _i1.companyID FROM ( SELECT companyID FROM Companies JOIN Zipcodes ON Zipcodes.zipcode = Companies.companyZipcode WHERE Companies.companyType = 'plumbing' ) _i1 Time: .8s
Without the outer query: .00x seconds

There are indexes on Zipcodes.zipcode and Companies.companyZipcode and Companies.companyType and Companies.companyID is the Primary Key.

Doing an EXPLAIN shows that the outer query causes MySQL to have a select_type of Derived instead of Simple.

Augie Gardner
  • 2,749
  • 3
  • 25
  • 36
  • While I don't understand why you're using an outer query here, did you try removing the alias? Does that have an influence on performance? Also JOINs create new (temporary) tables. Try using the second table directly using multiple tables in FROM. – Benjamin Schmidt Dec 11 '14 at 21:20
  • @bluefirex the outer query in this example is obviously not necessary, but is an example of a minimal instance where the problem I'm trying to solve arises. In an actual query, maybe we'd do some aggregation/group by on the outside, etc. – Augie Gardner Dec 11 '14 at 22:09

1 Answers1

0

When you have subqueries a temporary table needs to be created and data inserted into that.

dan b
  • 1,172
  • 8
  • 20