The fastest way to select the lowest value in another table

Question

I have an index table with trips that people can order, and a table with prices for these trips.

Tables

Travel

[tripid] [city] [province] [country] [culture] [nature] [rating]

Prices

[tripid] [date] [duration] [price]

Problem

When a user searches, I want to show the lowest available price. I am using the following query

SELECT t.*, 
(SELECT MIN(price) FROM prices WHERE tripid = t.tripid) 
FROM trips t;

This works, however, slowly. 5 seconds for 4000 results. Is there a faster way to achieve this?

Answer 1

Your request is in order:

SELECT t.*, 
       (SELECT MIN(p.price) FROM prices p WHERE p.tripid = t.tripid) 
FROM trips t;

You need an index on prices(tripid, price). This will improve performance.

Without an index, it is possible that pre-aggregation will be faster:

select t.*, minp
from trips t left join
     (select p.tripid, min(p.price) as minp
      from prices p
      group by p.tripid
     ) p
     on t.tripid = p.tripid;

prices , .