60 million entries, select entries from a certain month. How to optimize database?
Solution 1:
To get entries in a particular month, for a particular year, faster - you will need to index the time column:
CREATE INDEX idx_time ON ENTRIES(time) USING BTREE;
Additionally, use:
SELECT e.*
FROM ENTRIES e
WHERE e.time BETWEEN '2010-04-01' AND DATE_SUB('2010-05-01' INTERVAL 1 SECOND)
...because BETWEEN is inclusive, so you'd get anything dated "2010-05-01 00:00:00" with the query you posted.
I'd also like to select data from certain month from a given DataSourceID
You can either add a separate index for the datasourceid column:
CREATE INDEX idx_time ON ENTRIES(datasourceid) USING BTREE;
...or setup a covering index to include both columns:
CREATE INDEX idx_time ON ENTRIES(time, datasourceid) USING BTREE;
A covering index requires that the leftmost columns have to be used in the query for the index to be used. In this example, having time first will work for both situations you mentioned -- datasourceid doesn't have to be used for the index to be of use. But, you have to test your queries by viewing the EXPLAIN output to really know what works best for your data & the queries being performed on that data.
That said, indexes will slow down INSERT, UPDATE and DELETE statements. And an index doesn't provide a lot of value if the column data is has few distinct values - IE: a boolean column is a bad choice to index, because the cardinality is low.
Solution 2:
Take advantage of innodb clustered primary key indexes.
http://dev.mysql.com/doc/refman/5.0/en/innodb-index-types.html
This will be extremely performant:
create table datasources
(
year_id smallint unsigned not null,
month_id tinyint unsigned not null,
datasource_id tinyint unsigned not null,
id int unsigned not null, -- needed for uniqueness
data int unsigned not null default 0,
primary key (year_id, month_id, datasource_id, id)
)
engine=innodb;
select * from datasources where year_id = 2011 and month_id between 1 and 3;
select * from datasources where year_id = 2011 and month_id = 4 and datasouce_id = 100;
-- etc..
EDIT 2
Forgot i was running the first test script with 3 months of data. Here's the results for a single month : 0.34 and 0.69 seconds.
select d.* from datasources d where d.year_id = 2010 and d.month_id = 3 and datasource_id = 100 order by d.id desc limit 10;
+---------+----------+---------------+---------+-------+
| year_id | month_id | datasource_id | id | data |
+---------+----------+---------------+---------+-------+
| 2010 | 3 | 100 | 3290330 | 38434 |
| 2010 | 3 | 100 | 3290329 | 9988 |
| 2010 | 3 | 100 | 3290328 | 25680 |
| 2010 | 3 | 100 | 3290327 | 17627 |
| 2010 | 3 | 100 | 3290326 | 64508 |
| 2010 | 3 | 100 | 3290325 | 14257 |
| 2010 | 3 | 100 | 3290324 | 45950 |
| 2010 | 3 | 100 | 3290323 | 49986 |
| 2010 | 3 | 100 | 3290322 | 2459 |
| 2010 | 3 | 100 | 3290321 | 52971 |
+---------+----------+---------------+---------+-------+
10 rows in set (0.34 sec)
select d.* from datasources d where d.year_id = 2010 and d.month_id = 3 order by d.id desc limit 10;
+---------+----------+---------------+---------+-------+
| year_id | month_id | datasource_id | id | data |
+---------+----------+---------------+---------+-------+
| 2010 | 3 | 116 | 3450346 | 42455 |
| 2010 | 3 | 116 | 3450345 | 64039 |
| 2010 | 3 | 116 | 3450344 | 27046 |
| 2010 | 3 | 116 | 3450343 | 23730 |
| 2010 | 3 | 116 | 3450342 | 52380 |
| 2010 | 3 | 116 | 3450341 | 35700 |
| 2010 | 3 | 116 | 3450340 | 20195 |
| 2010 | 3 | 116 | 3450339 | 21758 |
| 2010 | 3 | 116 | 3450338 | 51378 |
| 2010 | 3 | 116 | 3450337 | 34687 |
+---------+----------+---------------+---------+-------+
10 rows in set (0.69 sec)
EDIT 1
Decided to test the above schema with approx. 60 million rows spread over 3 years. Each query is run cold i.e. each run separately after which mysql is restarted clearing any buffers and with no query caching.
The full test script can be found here : http://pastie.org/1723506 or below...
As you can see it's a pretty performant schema even on my humble desktop :)
select count(*) from datasources;
+----------+
| count(*) |
+----------+
| 60306030 |
+----------+
select count(*) from datasources where year_id = 2010;
+----------+
| count(*) |
+----------+
| 16691669 |
+----------+
select
year_id, month_id, count(*) as counter
from
datasources
where
year_id = 2010
group by
year_id, month_id;
+---------+----------+---------+
| year_id | month_id | counter |
+---------+----------+---------+
| 2010 | 1 | 1080108 |
| 2010 | 2 | 1210121 |
| 2010 | 3 | 1160116 |
| 2010 | 4 | 1300130 |
| 2010 | 5 | 1860186 |
| 2010 | 6 | 1220122 |
| 2010 | 7 | 1250125 |
| 2010 | 8 | 1460146 |
| 2010 | 9 | 1730173 |
| 2010 | 10 | 1490149 |
| 2010 | 11 | 1570157 |
| 2010 | 12 | 1360136 |
+---------+----------+---------+
12 rows in set (5.92 sec)
select
count(*) as counter
from
datasources d
where
d.year_id = 2010 and d.month_id between 1 and 3 and datasource_id = 100;
+---------+
| counter |
+---------+
| 30003 |
+---------+
1 row in set (1.04 sec)
explain
select
d.*
from
datasources d
where
d.year_id = 2010 and d.month_id between 1 and 3 and datasource_id = 100
order by
d.id desc limit 10;
+----+-------------+-------+-------+---------------+---------+---------+------+---------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref |rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+---------+-----------------------------+
| 1 | SIMPLE | d | range | PRIMARY | PRIMARY | 4 | NULL |4451372 | Using where; Using filesort |
+----+-------------+-------+-------+---------------+---------+---------+------+---------+-----------------------------+
1 row in set (0.00 sec)
select
d.*
from
datasources d
where
d.year_id = 2010 and d.month_id between 1 and 3 and datasource_id = 100
order by
d.id desc limit 10;
+---------+----------+---------------+---------+-------+
| year_id | month_id | datasource_id | id | data |
+---------+----------+---------------+---------+-------+
| 2010 | 3 | 100 | 3290330 | 38434 |
| 2010 | 3 | 100 | 3290329 | 9988 |
| 2010 | 3 | 100 | 3290328 | 25680 |
| 2010 | 3 | 100 | 3290327 | 17627 |
| 2010 | 3 | 100 | 3290326 | 64508 |
| 2010 | 3 | 100 | 3290325 | 14257 |
| 2010 | 3 | 100 | 3290324 | 45950 |
| 2010 | 3 | 100 | 3290323 | 49986 |
| 2010 | 3 | 100 | 3290322 | 2459 |
| 2010 | 3 | 100 | 3290321 | 52971 |
+---------+----------+---------------+---------+-------+
10 rows in set (0.98 sec)
select
count(*) as counter
from
datasources d
where
d.year_id = 2010 and d.month_id between 1 and 3;
+---------+
| counter |
+---------+
| 3450345 |
+---------+
1 row in set (1.64 sec)
explain
select
d.*
from
datasources d
where
d.year_id = 2010 and d.month_id between 1 and 3
order by
d.id desc limit 10;
+----+-------------+-------+-------+---------------+---------+---------+------+---------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref |rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+---------+-----------------------------+
| 1 | SIMPLE | d | range | PRIMARY | PRIMARY | 3 | NULL |6566916 | Using where; Using filesort |
+----+-------------+-------+-------+---------------+---------+---------+------+---------+-----------------------------+
1 row in set (0.00 sec)
select
d.*
from
datasources d
where
d.year_id = 2010 and d.month_id between 1 and 3
order by
d.id desc limit 10;
+---------+----------+---------------+---------+-------+
| year_id | month_id | datasource_id | id | data |
+---------+----------+---------------+---------+-------+
| 2010 | 3 | 116 | 3450346 | 42455 |
| 2010 | 3 | 116 | 3450345 | 64039 |
| 2010 | 3 | 116 | 3450344 | 27046 |
| 2010 | 3 | 116 | 3450343 | 23730 |
| 2010 | 3 | 116 | 3450342 | 52380 |
| 2010 | 3 | 116 | 3450341 | 35700 |
| 2010 | 3 | 116 | 3450340 | 20195 |
| 2010 | 3 | 116 | 3450339 | 21758 |
| 2010 | 3 | 116 | 3450338 | 51378 |
| 2010 | 3 | 116 | 3450337 | 34687 |
+---------+----------+---------------+---------+-------+
10 rows in set (1.98 sec)
Hope this helps :)
Solution 3:
You could use an index to trade disk usage for query speed. An index that starts the time column can speed up queries that ask for a particular month:
create index IX_YourTable_Date on YourTable (time, DataSourceID, ID, SomeData)
Because the index starts with the time field, MySQL can do a key range scan on the index. That should be as fast as it gets. The index should include all columns in the query, or MySQL would have to look from the index to the table data for each row. Since you're asking for 2 million rows, MySQL will likely ignore an index that is not covering. (Covering index = index that includes all rows in the query.)
If you never query on ID, you can redefine the table to use (time, DataSourceID, ID) as primary key:
alter table YourTable add primary key (time, DataSourceID, ID)
This will speed up searches on time at no cost in disk space, but searches on ID will be very slow.