MySQL Forums
Forum List  »  Merge Storage Engine

Historical data indexing
Posted by: Dominique PRUNIER
Date: March 13, 2006 01:18PM

Hi,

I have a question about the way MySQL buffers multiple column indexes (MyIsam sorted index). My tables looks like that:

CREATE TABLE `idx_v_t` (
`variable` bigint(20) NOT NULL default '0',
`timestamp` int(11) NOT NULL default '0',
`value` float NOT NULL,
PRIMARY KEY (`variable`,`timestamp`)
) ENGINE=MyISAM;

or

CREATE TABLE `idx_t_v` (
`variable` bigint(20) NOT NULL default '0',
`timestamp` int(11) NOT NULL default '0',
`value` float NOT NULL,
PRIMARY KEY (`timestamp`,`variable`)
) ENGINE=MyISAM;


I always have a primary index on the id column and the timestamp column. This index is very big. I merge several tables like this one using the MERGE engine (horizontal partitionning on timestamp column).

The way MySQL query merge tables makes me think it will use every underlying table index thus i have a big performance impact (I/O) loading such big indexes from many tables while only a little part is usefull (timestamp range).

My question is: Can MySQL cache the leftmost part of a multi-column index ONLY so that it can quickly find out that some tables won't contain relavant data ? Then it would be a BIG difference beetween having as primary key (timestamp,variable) instead of (variable, timestamp) since i only have a few different timestamps per table. Indeed, here is my cardinality for :

index on variable,timestamp
column 1 variable: 43212
column 2 timestamp: 10154922

index on timestamp variable
column 1 timestamp: 236
column 2 variable: 10154922

Needless to say that typical queries looks like:
SELECT * from `table` WHERE timestamp BETWEEN t1 AND t2 AND variable=id

Thanks.

Options: ReplyQuote


Subject
Views
Written By
Posted
Historical data indexing
7416
March 13, 2006 01:18PM
4021
March 14, 2006 11:37AM


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.