Probably not, ratecodes_map is only about 30 records. It's a table that needs to be edited by certain users to change the way this report is grouped. I assume by denormalized olap you mean store the ratecodes_map.rate_code info on the main reservations table and group by it? If i did that then everytime they changed the mapping it would take an incredibly long time to update the ~40mil recordset.
I put an index on ratecodes_map (rate_code) but it did not seem to speed up the query any.
Oh i guess i modified my original query some, which would explain while i'm joining and grouping like this.
SELECT pi.property_id,
rs.departure,
IFNULL(rc.rate_code,'undefined') AS rate_code,
IFNULL(rc.rate_desc,'undefined') AS rate_desc,
SUM(rs.nights) AS ty_rooms_sold,
SUM(rs.roomnet) AS ty_room_rev
FROM reservations AS rs
INNER JOIN property_info AS pi
ON (pi.property_id = rs.property_id)
LEFT JOIN ratecodes AS rcs
ON ( rs.ratecode = rcs.rate_code )
AND ( pi.property_id = rcs.propertyid )
LEFT JOIN ratecodes_map AS rc
ON ( rcs.ratecode_map = rc.map_id )
WHERE rs.departure >= '2012-01-01' AND rs.departure <= '2012-02-01'
AND pi.property_id = pi.property_id
GROUP BY rc.rate_code, pi.property_id
ORDER BY pi.property_id
;
I forgot to join in ratecodes on the first example to get the actual map id. so i join rs.ratecodes -> rcs.rate_code -> rc.rate_code (which is the value i need - the mapping code)
Edited 3 time(s). Last edit at 03/08/2012 02:53PM by Chris T.