Subquery or a double query?

Question

Hi all,

For a client I have built a travelagency website. Now they asked me to optimize and automize some things. One of these things is that the accommodations shown in the searchlist must be bookable.

Simplified this is the structure

I have an AccommodationObject, an Accommodation has 1 or more PricePeriods.

What I need to do pick out the last priceperiod connected to a given accommodation. The priceperiod has a field 'date_until', which is a timestamp. In the query a check must be made, current timestamp must be less than the last timestamp from a priceperiod.

This is easy to do with a second query which just gets the last timestamp from the priceperiod table from all the priceperiod-rows connected to the given accommodation.

I'm wondering if this is the best case, or if I should use a subquery for this. And if so, how would a query like this look? I don't have much experience with subqueries.

Hope to hear from you!

Thanks in advance for the effort you take to write an answer!

Ben Fransen

Update

Table structure (simple)

Accommodation ->ID

PricePeriod -> ID | AccommodationID | DateUntil

Simplified:

SELECT fieldlist FROM Accommodation WHERE ID = $id
SELECT MAX(DateUntil) FROM PricePeriod WHERE AccommodationID = $id

But I would like to this in one query. I hope it's clear this way..

Answer 1

It depends upon a number of things, but what you should do is try a few different alternatives.

Personally, I would do:

SELECT fieldlist, 
(SELECT MAX(DateUntil) FROM PricePeriod) WHERE AccomidationID = a.id) AS last_date
FROM Accommodation AS a
WHERE a.id = $id

You could also do (But it may not be as efficient)...:

SELECT fieldlist, MAX(b.DateUntil)
FROM Accommodation AS a
JOIN PricePeriod AS b ON a.id = b.AccommodationID
WHERE a.id = $id
GROUP BY a.id

The big thing is to try them all (Be sure to use SQL_NO_CACHE so that the results are true each time). Run them through EXPLAIN to try to see how "heavy" they are...