CODEWITHSUNDEEP
mysqlperformanceunionsqlperformancesubquery
David
David

Reputation: 2895

SQL Performance - Union and Subselects

I have three tables which have a common field which is NOT its identifier. For example:

dairy_warehouse
   -dairyId (primary key)
   -expiration_date
   -other dairy related fields

frozen_food_warehouse
   -frozenFoodId (primary key)
   -expiration_date
   -other frozen food related fields (not same as dairy related fields)

dry_goods_warehouse
   -dryGoodsId (primary key)
   -expiration_date
   -other dry goods related fields (not same as frozen food or dairy fields)

I'm trying to build a report that lists all items which are expiring in the next month. I don't think a join would work since there is no foreign key relationship between the three tables. I'm currently leaning towards adding an index on expiration_date and using a union and 3 sub-selects, but I'm concerned that this will have terrible performance. Is there a better solution?

Upvotes: 0

Views: 160

Answers (2)

jklemmack
jklemmack

Reputation: 3636

A foreign key is for referential integrity - to make sure a "child" table cannot specify a value that does not exist in a "master" table*. Foreign keys, by themselves, don't speed SELECT query performance. You thought of adding an index on "expiration_date" should result in a performant query.

  • There are plenty of other scenarios for using RI and foreign keys, but this is the most common scenario.

Upvotes: 0

Brad
Brad

Reputation: 15879

Using a Union will not affect performance as long as each query is hitting an index. Your suggestion to add an index on expiration_date is the correct thing to do.

There is no foreign key because I would say none of the records are related between tables.

Upvotes: 2

Related Questions