Skip navigation links

MySQL Forums :: Microsoft SQL Server :: How to speed up select on a large table


Advanced Search

How to speed up select on a large table
Posted by: Dmitrii Dimandt ()
Date: December 22, 2008 03:29AM

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!

Options: ReplyQuote


Subject Written By Posted
How to speed up select on a large table Dmitrii Dimandt 12/22/2008 03:29AM


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.