I have 2 tables, "locations" and "travels"
location_id, subdivision, city, region, country, continent
date, src_location_id, dst_location_id, duration
I want to display statistics based on location, something like:
"travels to mount everest by country" or "most popular summer vacation cities by country"
my program assembles an SQL statement that looks like this:
SELECT loc1.city, loc2.region, COUNT(*), SUM(t.duration)
FROM travels t
LEFT OUTER JOIN locations loc1 ON t.src_location_id = loc1.location_ID
LEFT OUTER JOIN locations loc2 ON t.dst_location_id = loc2.location_ID
/*WHERE date = xxx, etc etc*/
GROUP BY loc1.city, loc2.region
The location table contains thousands of locations, and compounded with tens of thousands of travel data, it's just too slow.
I tried to create a materialized view that appended src_city, dst_city, src_region, dst_region, etc... to the travels table,
but it seems that I can't perform fast refreshes on joined tables.