Innodb vs myisam


#1

Hi,
I have been using myisam tables in mysql with rails because my client
will soon want fulltext searchable content, however when I migrated my
development db from schema.rb all of the tables generated were innodb. I
understand that I can override this, but also like the transactions and
foreign keys of innodb. So 2 questions really:

  1. are transactions in activerecord dependent on innodb or do they
    recreate that functionality?
  2. if I did stick with innodb would there be a workaround for effective
    text searching?

thanks

dorian


I do things for love or money


#2

Hi !

2006/3/24, Dorian M. removed_email_address@domain.invalid:

  1. are transactions in activerecord dependent on innodb or do they recreate that functionality?

ActiveRecord builds on the underlying database engine to provide
transactional support. If you have MyISAM tables, forget about
transactions. That being said, there’s nothing preventing you from
having different table engines in the same database.

  1. if I did stick with innodb would there be a workaround for effective text searching?

Take a look at Ferret, Indexed Search Engine and ActiveSearch Plugin:

http://wiki.rubyonrails.com/rails/pages/HowToIntegrateFerretWithRails
http://wiki.rubyonrails.org/rails/pages/Indexed+Search+Engine
http://wiki.rubyonrails.org/rails/pages/Active+Search+Plugin

Hope that helps !


#3

Hi Dorian,

  1. are transactions in activerecord dependent on innodb or do they
    recreate that functionality?

The transactions in ActiveRecord are dependent on the underlying
database to provide the transaction support since the transaction API
only creates the required transaction SQL.

That also means you cannot use transactions with MyISAM. Must use
InnoDB or BDB.

  1. if I did stick with innodb would there be a workaround for
    effective text searching?

Yes, some possibilities include:

  1. If using replication, have your master table type as InnoDB and
    slave table type as MyISAM. Then run all the SELECT statements on
    slaves.
  2. Use Lucene (See Erik H.'s book) or Ferret for your project.

There’s some interesting discussion on my blog about InnoDB vs.
MyISAM.
http://mysqldatabaseadministration.blogspot.com/2006/02/innodb-or-myisam-whats-your-preference.html

You may also want to see:
http://wiki.rubyonrails.com/rails/pages/HowToUseTransactions

If you need to know how to integrate Lucene, I can provide you with
code.

Hope this helps.

Frank

Dorian M. removed_email_address@domain.invalid wrote: Hi,
I have been using myisam tables in mysql with rails because my client
will soon want fulltext searchable content, however when I migrated my
development db from schema.rb all of the tables generated were innodb.
I understand that I can override this, but also like the transactions
and foreign keys of innodb. So 2 questions really:

  1. are transactions in activerecord dependent on innodb or do they
    recreate that functionality?
  2. if I did stick with innodb would there be a workaround for effective
    text searching?

thanks

dorian


I do things for love or money


Rails mailing list
removed_email_address@domain.invalid
http://lists.rubyonrails.org/mailman/listinfo/rails

Want to read my Pro Rails book or Pro Server Management book? Become a
beta reader now. Write me to learn more.

Rails Blog: http://railsruby.blogspot.com
MySQL Blog: http://mysqldatabaseadministration.blogspot.com
Linux / Security Blog: http://frankmash.blogspot.com
Programming One Liners: http://programming-oneliners.blogspot.com


#4

http://www.postgresql.org/


View this message in context:
http://www.nabble.com/innodb-vs-myisam-t1334763.html#a3582672
Sent from the RubyOnRails Users forum at Nabble.com.


#5

good answer… :smiley:

b


#6

On Fri, 2006-03-24 at 00:43 -0500, Dorian M. wrote:

  1. are transactions in activerecord dependent on innodb or do they
    recreate that functionality?

Transactions in Rails require that you use a database that supports
transactions. With MySQL, you need to use InnoDB (which Oracle now
owns…).

  1. if I did stick with innodb would there be a workaround for
    effective text searching?

You need to do some fun stuff to make that work.

If you aren’t forced to stay on MySQL, you might consider another open
source database… PostgreSQL.

Transactional support + tsearch2 = relational data integrity + smart
full text searching.

More info:

-Robby


/**************************************************************

  • Robby R., Founder & Executive Director *
  • PLANET ARGON, LLC | www.planetargon.com *
  • Ruby on Rails Development, Consulting, and Hosting *
  • Portland, Oregon | p: 503.351.4730 | f: 815.642.4068 *
  • blog: www.robbyonrails.com | book: www.programmingrails.com *
    ***************************************************************/

#7

On 3/25/06, Robby R. removed_email_address@domain.invalid wrote:

-Robby

So, once again I’ll demonstrate just how really dense I am, in hopes of
learning a thing or two :slight_smile:

I have tried no less than three time before to set up postgresql on my
Debian system using apt-get. It installs without a hitch, of course, but
that’s as far as I can get. I have never been able to successfully
create a
user account, much less a db or any tables that I can use.

I have tried to RTFM and GTFW (many times) on the subject, but I’ve yet
to
find any tutorial or howto that really works with the default Debian
setup
(on Debian unstable, if that matters). Can anyone point me to any Debian
specific references, that they can verify have worked for them?

The one thing I’ve not tried is downloading the source tar.gz and
configuring/compiling it myself. Perhaps this is the best way?

Thanks In Advance,

Howard


#8

Howard R. wrote:

  • http://www.postgresql.org/
    Debian system using apt-get. It installs without a hitch, of course, but
    that’s as far as I can get. I have never been able to successfully create a
    user account, much less a db or any tables that I can use.

It’s really simple, once you know how:

As root:

su - postgres
createuser yourusername

at this point it asks if you want to be able to create databases, users
and so on. Say yes.

Now you can, from a shell as yourself, do commands like:

createdb foobar

psql foobar_development

dropdb foobar_development

createuser rails

and so on… Of course you can do that from the DB too, but it’s
generally more convenient to do it from the command line.

The permissions in pg_hba.conf might take some fiddling with, but it’s
nothing that difficult.

Once you get going with Postgresql, it’s hard to use Mysql again, IMO
(although in fairness, perhaps 5.0, with InnoDB is starting to be an ok
system).


David N. Welton

Linux, Open Source Consulting


#9

David,
Thanks for the reply.

It’s really simple, once you know how:

As root:

su - postgres
createuser yourusername

~$ su
Password:
/home/howardroberts# su - postgres
/home/howardroberts# createuser howardroberts
Shall the new role be a superuser? (y/n) y
createuser: could not connect to database postgres: FATAL: role “root”
does
not exist
khaosdebian:/home/howardroberts#

The permissions in pg_hba.conf might take some fiddling with, but it’s
nothing that difficult.

I think they might…

Howard


#10

~$ su
Password:
/home/howardroberts# su - postgres
/home/howardroberts# createuser howardroberts

You should have gotten a $ prompt, not a # prompt. You’re doing
something wrong…

Shall the new role be a superuser? (y/n) y
createuser: could not connect to database postgres: FATAL: role “root” does
not exist

Indeed, you’re still root. Perhaps the postgres user doesn’t have a
shell?

khaosdebian:/home/howardroberts#

The permissions in pg_hba.conf might take some fiddling with, but it’s
nothing that difficult.

I think they might…

That doesn’t have anything to do with the above problem.


David N. Welton

Linux, Open Source Consulting


#11

You might also consider firebird (formerly Interbase, formerly RDB). It
has a successful history spanning nearly 30 years on many platforms,
both as a commercial engine and as an open source engine.

http://www.ibphoenix.com