MySQL Forums
Forum List  »  Newbie

Help optimizing query/index
Posted by: Chris T
Date: March 08, 2012 09:13AM

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!

Options: ReplyQuote


Subject
Written By
Posted
Help optimizing query/index
March 08, 2012 09:13AM
March 08, 2012 10:09AM
March 08, 2012 12:18PM
March 08, 2012 12:23PM
March 08, 2012 02:40PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.