Size is an issue with InnoDB and deleting records does not reduce the size of the file. In
my experience, the performance drop off
is considerable once the table reaches a certain size. And it's not a slight drop off over
If your table is going to get very large, I would reccommend using MyISAM. You have many
more options for managing a large table.
For instance, you can split the data up into table, say one per month. You can then create
merge tables to access those tables. This
gives you the ability to create datasets of varying sizes without the need to change any
code. Modifying a merge table is quick and
easy, so you can create a sliding 3, 6 and 12 month tables. The underlying table stay the
If you expect many inserts while long searches are going on, MyISAM will be a problem. The
searches will block the inserts, forcing
them to queue up. Depending on how busy the box is, this can become a problem. InnoDB
won't have this issue, but it is slower.
I've actually done hybrid setups where insert tables are InnoDB and "search" tables are
MyISAM. Periodically (i.e. daily) the InnoDB
data is imported into the MyISAM tables, then dropped and recreated. Your code needs to
know that it needs to do a UNION between the
MyISAM and InnoDB table to get the most up to date information. While more complicated, it
has actually worked fairly well. At least
up to about 450 million rows so far.
----- Original Message -----
From: "Olaf Stein" <steino@stripped>
To: "MySql" <mysql@stripped>
Sent: Thursday, January 25, 2007 4:17 PM
Subject: Innodb, why not?
> Hi All
> I know the innodb vs myisam issue comes up quite frequently. I went through
> old threads and could not find an answer to my questions.
> Generally, is there any reason/scenario not to use innodb?
> From a feature perspective, I do not need full text indices, foreign keys
> are usefull but not necessary (if I write the applications accordingly),
> transactions are also usefull but not entirely necessary.
> Basically I have no excluding reasons for the one or the other.
> From a speed perspective, I do not have a lot of simultaneous connections
> but a lot of data. Some tables have several hundred million records
> (growing). I read somewhere that innodb loses performance once the size of
> the tables exceed the amount of RAM. Is that true and if yes, how bad is
> that loss?
> I know this is a very general question but it seems not to make any sense
> not to use innodb having such exotic features like foreign keys and
> Maybe some of you had this dilemma in the past and can offer some insight.
> Thanks in advance
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=1