I'm dealing with databases right now while keeping an eye on «High performance MySQL». Finally decided to try something out. Here we go...

We have a table split into partitions be week:


CREATE TABLE `price_detail` (
`accomodation` int(11) NOT NULL,
`date` datetime NOT NULL,
`price` int(11) NOT NULL,
`room_id` int(11) NOT NULL,
KEY `idx_price_date` (`date`) USING BTREE,
KEY `idx_price` (`price`) USING BTREE,
KEY `idx_price_accomodation` (`accomodation`) USING BTREE
)
PARTITION BY RANGE (TO_DAYS(`date`)) (
PARTITION p20080101 VALUES LESS THAN (733407),
PARTITION p20080108 VALUES LESS THAN (733414),
/* and so on — an entire years worth */


Yep... We have indexes as well...

We have 26 million rows per week in the worst case. Needless to say that we have the worst case for testing purposes .

Let's see how a simple query will work on such a database:

Query I:
explain partitions select accomodation from price_detail where accomodation=1 and date BETWEEN '2008-01-01 12:00:00.000' AND '2008-01-14 12:00:00.000'G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: price_detail partitions: p20080108,p20080115 type: ref possible_keys: idx_price_date,idx_price_accomodation key: idx_price_accomodation key_len: 4 ref: const rows: 40292 Extra: Using where

Query II:

explain partitions select accomodation from price_detail where date BETWEEN '2008-01-01 12:00:00.000' AND '2008-01-14 12:00:00.000 AND accomodation=1'G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: price_detail partitions: p20080108,p20080115,p20080122,p20080129,p20080205,p20080212,p20080219, p20080226,p20080304,p20080311,p20080318,p20080325,p20080401,p20080408, p20080415,p20080422,p20080429,p20080506,p20080513,p20080520,p20080527, p20080603,p20080610,p20080617,p20080624,p20080701,p20080708,p20080715, p20080722,p20080729,p20080805,p20080812,p20080819,p20080826,p20080902, p20080909,p20080916,p20080923,p20080930,p20081007,p20081014,p20081021, p20081028,p20081104,p20081111,p20081118,p20081125,p20081202,p20081209, p20081216,p20081223,p20081230,p20090106,p20090113,p20090120,p20090127 type: ALL possible_keys: idx_price_date key: NULL key_len: NULL ref: NULL rows: 274845000 Extra: Using where

What a nasty surprise!

I'm off learning about covering indices...