Complex SQL query, need to sort via count based upon time constraints

Hi guys I have the following three tables here.

COUNTRIES
ID | Name | Details

Airports
ID | NAME | CountryID

Trips
ID | AirportID | Date

I have to retrieve a list showing the following:

AirportID | AIrport Name | Country Name | Number of Trips Made Between Date1 and Date2

I need this to be really efficient, what kind of indexes do I need to set up and how would I formulate the SQL query here? I would be displaying this 开发者_StackOverflow社区using Php. Note that I need to be able to sort based upon the number of trips made.

EDIT ==

Oops forgot to mention my sql:

I've tried the following:

SELECT `c`.*, `t`.`country` AS `country_name`, COUNT(f.`id`) AS `num_trips` FROM `airports` AS `c`
 LEFT JOIN `countries` AS `t` ON  t.`id` = c.`country_id` 
 LEFT JOIN `trips` AS `f` ON f.`airportid` = c.`id` GROUP BY `c`.`id` ORDER BY `num_flights` ASC LIMIT 10

It works but takes a really looong time to execute - plus consider this that my airports table has over 30'000 entries and teh trips table is variable.

I'm just taking the name of the country from the countries table - would it be better if I were to instead exclude joining teh countries table in the sql and instead retrieve the country name from an array where the index is the ID and values are the names of countries?

---

I'm not sure why you're using left joins. If every trip has an airport and every airport has a country, and inner join would give you accurate results.

I would do this:

select a.ID as AirportID, a.Name as AirportName, c.Name as CountryName, count(t.id) as NumTrips from Trips t inner join Airports a on t.AirportID = a.ID inner join Countries c on a.CountryID = c.ID where t.Date >= @StartDate and t.Date <= @EndDate group by AirportID, AirportName, CountryName order by NumTrips limit 10

Replace the @StartDate and @EndDate with your appropriate values.

Not sure what you're looking for in results, but I would expect you want the most trips. In that case you would want to do "order by NumTrips desc". This will show the highest values first, especially since you're limiting it to 10.

Also, I suggest you rename your "Date" column to something that won't collide with reserved SQL words. I usually use "DateCreated" or "DateOfTravel" or something like that.

If I made any poor assumptions let me know and I can re-write this.

Edit:

For indexes, create them on fields you will be looking up on. In other words, primary keys (which should always be indexed), foreign keys, and in this case it looks like the Date column would be the other important index. However, if you plan on searching by "Airport Name", then add an index there. I think you see where this is headed, etc.

---

Indexes on airpoirt(countryid, id) and trips(airportid) would seem the most important.

Instead of count(f.id) try count(f.airportid), so MySQL doesn't have to check the trips.id column.