MySQL Forums
Forum List  »  Performance

Choosing InnoDB vs. MyISAM
Posted by: Sean Forman
Date: June 03, 2006 08:50PM

I have looked around for the answer, but haven't been able to nail it down.

I have a table 7.4m rows. They are essentially every play in major league baseball from 1957 to 2005. The table will be set up and then have NO updates or inserts. I am essentially looking purely to maximize lookup speed.

I know indexing is vital and I have a grasp on that.

I have split the Play-by-play data into two tables, the general important info (it is fixed row format), and the details (it is dynamic row format).

The types of queries that I expect to do are: Find all of the home runs by Hank Aaron. Find all of the home runs by Hank Aaron against the Dodgers, etc. Find all of the Phillies triples against the Mets in Shea, etc. I also aggregate data using group by commands.

Players and teams are currently represented by alphanumeric id's. The primary table (pbp_basic) has an auto_increment primary key to identify the rows, which I then reference to the pbp_detail_fixed and pbp_detail_var tables. Should these tables be myisam or innodb or a mix? and how does one generally decide these issues? Again the tables will be read only.

One other question. I know that I can save about 500MB of data storage by converting to a numeric key for the 17,000 or so players in mlb history, but is there a speed issue that suggests I do that as well. The total table size is around 2GB now. I have a number of other tables that I'm using as well.

Other questions, that I have had.
What other things can I do for a read only table?

I'm planning on putting the player name lookup tables and some others as memory tables.

Should I use myisampack? Will that improve performance or just save disk space?

I also read about filling the index trees? Does that cause a significant performance gain?

The machine this will be housed on will be the DB only and has 6GB of RAM and a RAID 5 HD with dual processors.

I appreciate the help.

sean
http://www.baseball-reference.com/

Options: ReplyQuote


Subject
Views
Written By
Posted
Choosing InnoDB vs. MyISAM
1617
June 03, 2006 08:50PM
960
June 04, 2006 12:02PM


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.