How to speed up select on a large table

[Dmitrii Dimandt]

Here's the problem:

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_accomodation` (`accomodation`) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=latin1 
PARTITION BY RANGE (TO_DAYS(`date`)) (
PARTITION p20080101 VALUES LESS THAN (TO_DAYS('2008-01-01')),
PARTITION p20080108 VALUES LESS THAN (TO_DAYS('2008-01-08')),
PARTITION p20080115 VALUES LESS THAN (TO_DAYS('2008-01-15'))
/* and so on, partitions every 7 days for the entire year */

I need this because I have (worst-case scenario) 3,780,000 records per day. Trust me on that :) This amounts to 26,460,000 records per partition

90% of queries will query for a date range of 7-10 days. So, 90% of queries will hit 1-3 partitions (up to 79,380,000 rows). They all amount to a simple select like these:

SELECT * FROM price_detail WHERE date BETWEEN ... AND ... AND accomodation=1

/* or */

SELECT price FROM price_detail WHERE date BETWEEN ... AND ... AND accomodation=1

I intend to insert the resulting resultset into a temporary table and do a SELECT with AVG/MIN/whatnot on that temp table later.

What I need is for the initial SELECT from the huge table to execute in under 2 seconds. Under 1 second wold be even better.

What I have now is a stock MacBook Pro :) with 2 GB of RAM and a MySQL 5.1 downloaded from MySQL site.

Currently,

SELECT count(accomodation) FROM price_detail WHERE date = '2008-01-03 12:00:00.000' AND accomodation=1;

+---------------------+
| count(accomodation) |
+---------------------+
|                3000 | 
+---------------------+
1 row in set (2.18 sec)

SELECT SQL_BIG_RESULT accomodation FROM price_detail WHERE date = '2008-01-04 12:00:00.000' AND accomodation=1;

/* lots of rows */
|            1 | 
|            1 | 
+--------------+
3000 rows in set (4.49 sec)

Doing a

CREATE TEMPORARY TABLE Temp SELECT * FROM price_detal WHERE BETWEEN ... AND ... AND accomodation=1

also runs in about 4-5 seconds.


I believe that these values are ok for a laptop computer. However, I desperately need some reassurance with this :)

I also need some pointers as to what to look for when optimizing the database (what numbers in the config file to increase, hat numbers to decrease and so on :) ). Also, how far can I grow with this?

Thanks for any tips!