Using MyISAM tables?

Rails
1

I was wondering if there were any drawbacks in using MyISAM tables
instead of InnoDB? Main reason is that I have limited memory on my VPS
and skipping innodb tables makes a considerable difference in memory
usage.

I know that rails migration create InnoDB tables by default, I was
wondering if there are any reasons for this or if I can just use MyISAM
without noticing any difference on the coding end… ?

Thank you!

2

On 12 Nov 2007, at 21:33, Jean-nicolas Jolivet wrote:

without noticing any difference on the coding end… ?
No foreign keys, no transactions, no row level locks etc…

Fred

3

Frederick C. wrote:

On 12 Nov 2007, at 21:33, Jean-nicolas Jolivet wrote:

without noticing any difference on the coding end… ?
No foreign keys, no transactions, no row level locks etc…

Fred

Gotcha. But does no foreign key means that model relations won’t work??

(I.e. if I have a User table with a address_id field…and an Address
table, with a has_one/belongs_to relation…can I still use User.address
to access his address info from the Address table?)

4

No, it just means you can’t enforce data integrity at the db layer and
will have to rely 100% on your models to enforce it.

-Bill

Jean-nicolas Jolivet wrote:

Gotcha. But does no foreign key means that model relations won’t work??

(I.e. if I have a User table with a address_id field…and an Address
table, with a has_one/belongs_to relation…can I still use User.address
to access his address info from the Address table?)

–
Sincerely,

William P.

5

Thank you all for the helpful replies! Exactly what I wanted to know!

6

Jean-nicolas Jolivet wrote:

I was wondering if there were any drawbacks in using MyISAM tables
instead of InnoDB? Main reason is that I have limited memory on my VPS
and skipping innodb tables makes a considerable difference in memory
usage.

You may want to try PostgreSQL then… When I started using a 64MB VPS I
tried MySQL hoping it was lightweight compared to the more feature-rich
PostgreSQL, I was disappointed.

I know that rails migration create InnoDB tables by default, I was
wondering if there are any reasons for this or if I can just use MyISAM
without noticing any difference on the coding end… ?

On the coding end, there’s no difference, but :

  • transactions will not be used without any warning (the SQL will be
    executed but no rollback will ever happen in case of failure),
  • concurrent access to the DB will be slow (no row-level locking),
    should not be a problem on a memory constrained system though,
  • you will rely entirely on ActiverRecord validations to enforce foreign
    keys (there are race conditions that can corrupt your database instead
    of triggering an exception),

Lionel