expected gain if table split into two or more tables
Hello,
I have a table that looks like this:
CREATE TABLE LSCmetadata (
oid INTEGER(11) NOT NULL AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
pset SMALLINT(4),
gpsStart INTEGER(10) UNSIGNED,
gpsEnd INTEGER(10) UNSIGNED,
runTag ENUM ('S1','S2','S3','S4','S5','S6','A4','M6',
'postS3','MDC','S2-Burst-MDC','S3-Burst-MDC','E12',
'trend'),
site SET ('A','G','H','L','T','V'),
frameType VARCHAR(64),
locked MEDIUMINT(7),
scienceMode MEDIUMINT(7),
coincidence MEDIUMINT(7),
interferometer SET ('A1','G1','H1','H2','L1','T1','V1','V2'),
duration INTEGER(10) UNSIGNED,
size INTEGER(10) UNSIGNED,
md5 VARCHAR(32),
ts TIMESTAMP NOT NULL,
PRIMARY KEY (oid),
UNIQUE INDEX (name),
INDEX (pset),
INDEX (gpsStart),
INDEX (gpsEnd),
INDEX (runTag),
INDEX (site),
INDEX (frameType),
INDEX (interferometer),
INDEX (ts),
INDEX (gpsStart,gpsEnd,frameType,site)
) TYPE=InnoDB;
I have about 14 million rows in this table. I am running on a Solaris 9 Sparc machine and am allowing 6 gigabytes of memory for innodb_buffer_pool_size. I am fairly happy with the performance of queries against this table, but I would not mind picking up even a 5% decrease in query time if it is not too much effort.
Since almost all queries against this table only involve 5 columns (gpsStart, gpsEnd, runTag, frameType, site) and the other columns are primarly there for archival purposes, I am wondering what performance gains I might expect if I split this table into two or more tables?
Specifically I am considering moving all other columns except for those important 5 (along with oid, name, and ts which need to stay) into a second table that is rarely queried against. I am asking you experts, based on your experience, what type of performance gain I might expect?
My naive thinking is that by reducing the amount of data in the table that is queried the most I will pick up a performance gain. But then I think that with the indexing being used already and the fast seek times of the disk I will not see any appreciable gain.
I appreciate hearing any insights you have.
Sincerely,
Scott Koranda