Okay so I have this table, reservations, its about 40million records, i need to query it based on a date and property range and group by a column to get statistics.. I've been playing aruond with the indexes the past few nights (take a long time to build new indexes so i usually do it overnight), and so far i can't reduce my query time. This is part of my main query(ran in sproc):
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
LEFT JOIN ratecodes_map AS rc
ON ( rs.ratecode = rc.rate_code )
INNER JOIN property_info AS pi
ON (pi.property_id = rs.property_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
;
This query takes 30-60seconds to complete. We have a similar system that's being replaced using mssql and a similar query takes about 3seconds.
This is the extended explain on it:
+----+-------------+-------+--------+--------------------------------------------------------------------------------------------------------------------------------------+-----------------------------+---------+---------------------------+--------+----------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+--------+--------------------------------------------------------------------------------------------------------------------------------------+-----------------------------+---------+---------------------------+--------+----------+----------------------------------------------+
| 1 | SIMPLE | rs | range | IX_reservations_extract_dup,IX_reservations_search_date,IX_reservations_roomstat,IX_reservations_ratestat,IX_reservations_ratesearch | IX_reservations_search_date | 4 | NULL | 579472 | 100.00 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | rc | ALL | NULL | NULL | NULL | NULL | 44 | 100.00 | |
| 1 | SIMPLE | pi | eq_ref | PRIMARY | PRIMARY | 3 | rribi_test.rs.property_id | 1 | 100.00 | Using where; Using index |
+----+-------------+-------+--------+--------------------------------------------------------------------------------------------------------------------------------------+-----------------------------+---------+---------------------------+--------+----------+----------------------------------------------+
Here is the indexes I have on that table:
+--------------+------------+-----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------+------------+-----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| reservations | 0 | PRIMARY | 1 | accountuid | A | 22919495 | NULL | NULL | | BTREE | | |
| reservations | 0 | PRIMARY | 2 | property_id | A | 22919495 | NULL | NULL | | BTREE | | |
| reservations | 0 | IX_reservations_extract_dup | 1 | property_id | A | 18 | NULL | NULL | | BTREE | | |
| reservations | 0 | IX_reservations_extract_dup | 2 | accountuid | A | 22919495 | NULL | NULL | | BTREE | | |
| reservations | 0 | IX_reservations_extract_dup | 3 | lastupdate | A | 22919495 | NULL | NULL | YES | BTREE | | |
| reservations | 1 | IX_reservations_search_date | 1 | departure | A | 18 | NULL | NULL | YES | BTREE | | |
| reservations | 1 | IX_reservations_search_date | 2 | arrival | A | 18 | NULL | NULL | YES | BTREE | | |
| reservations | 1 | IX_reservations_search_date | 3 | property_id | A | 2546610 | NULL | NULL | | BTREE | | |
| reservations | 1 | IX_reservations_roomstat | 1 | property_id | A | 18 | NULL | NULL | | BTREE | | |
| reservations | 1 | IX_reservations_roomstat | 2 | departure | A | 1091404 | NULL | NULL | YES | BTREE | | |
| reservations | 1 | IX_reservations_roomstat | 3 | roomtype | A | 7639831 | NULL | NULL | YES | BTREE | | |
| reservations | 1 | IX_reservations_ratestat | 1 | property_id | A | 18 | NULL | NULL | | BTREE | | |
| reservations | 1 | IX_reservations_ratestat | 2 | departure | A | 1145974 | NULL | NULL | YES | BTREE | | |
| reservations | 1 | IX_reservations_ratestat | 3 | ratecode | A | 11459747 | NULL | NULL | YES | BTREE | | |
| reservations | 1 | IX_reservations_lastupdate | 1 | lastupdate | A | 5729873 | NULL | NULL | YES | BTREE | | |
| reservations | 1 | IX_reservations_ratesearch | 1 | property_id | A | 18 | NULL | NULL | | BTREE | | |
| reservations | 1 | IX_reservations_ratesearch | 2 | ratecode | A | 14069 | NULL | NULL | YES | BTREE | | |
+--------------+------------+-----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
17 rows in set (0.49 sec)
Here is the describe of my table layout:
+------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+--------------+------+-----+---------+-------+
| property_id | char(4) | NO | PRI | NULL | |
| crsresnr | varchar(16) | NO | | | |
| accountuid | varchar(40) | NO | PRI | | |
| arrival | date | YES | | NULL | |
| departure | date | YES | MUL | NULL | |
| nights | varchar(3) | YES | | NULL | |
| status | varchar(8) | YES | | NULL | |
| roomtype | varchar(10) | YES | | NULL | |
| ratecode | varchar(14) | YES | | 0.00 | |
| roomrate | decimal(4,2) | YES | | 0.00 | |
| roomrev | decimal(7,2) | YES | | 0.00 | |
| roomnet | decimal(7,2) | YES | | 0.00 | |
| roomtax | decimal(7,2) | YES | | 0.00 | |
| roomnr | varchar(5) | YES | | NULL | |
| companyuid | varchar(40) | YES | | NULL | |
| receivablesuid | varchar(40) | YES | | NULL | |
| groupuid | varchar(40) | YES | | NULL | |
| traveluid | varchar(40) | YES | | NULL | |
| membernr | varchar(24) | YES | | NULL | |
| gtd | varchar(6) | YES | | NULL | |
| adult | int(2) | YES | | 0 | |
| child | int(2) | YES | | 0 | |
| source | varchar(12) | YES | | NULL | |
| lastupdate | datetime | YES | MUL | NULL | |
+------------------+--------------+------+-----+---------+-------+
I really just think i need to get the index layout right. I know mssql and mysql indexes are a bit different (like PK is in all indexes on mysql), but otherwise i have the same index that the mssql table uses for this query (IX_ratesearch).
I truly appreciate everyone taking the time to read through this, and any suggestions you have at all would be great.
Thanks!