Forum: Ruby on Rails innodb vs myisam

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Dorian M. (Guest)
on 2006-03-24 07:43
(Received via mailing list)
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
François B. (Guest)
on 2006-03-24 08:17
(Received via mailing list)
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.

> 2. 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/HowToInteg...
http://wiki.rubyonrails.org/rails/pages/Indexed+Se...
http://wiki.rubyonrails.org/rails/pages/Active+Search+Plugin

Hope that helps !
Frank (Guest)
on 2006-03-24 08:33
(Received via mailing list)
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.

  >> 2. 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/20...

  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
Lucifron (Guest)
on 2006-03-25 04:30
(Received via mailing list)
http://www.postgresql.org/

--
View this message in context:
http://www.nabble.com/innodb-vs-myisam-t1334763.ht...
Sent from the RubyOnRails Users forum at Nabble.com.
Ben M. (Guest)
on 2006-03-25 10:10
(Received via mailing list)
good answer... :-D

b
Robby R. (Guest)
on 2006-03-25 16:31
(Received via mailing list)
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...).

> 2. 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:

  - http://www.postgresql.org/
  - http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/


-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 *
***************************************************************/
Howard R. (Guest)
on 2006-03-25 18:19
(Received via mailing list)
On 3/25/06, Robby R. <removed_email_address@domain.invalid> wrote:
>   - http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/
>
>
> -Robby
>
So, once again I'll demonstrate just how really dense I am,  in hopes of
learning a thing or two :)

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
David N. Welton (Guest)
on 2006-03-25 18:32
(Received via mailing list)
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
- http://www.dedasys.com/davidw/

Linux, Open Source Consulting
- http://www.dedasys.com/
Howard R. (Guest)
on 2006-03-25 20:05
(Received via mailing list)
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
David N. Welton (Guest)
on 2006-03-25 21:52
(Received via mailing list)
> ~$ 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
- http://www.dedasys.com/davidw/

Linux, Open Source Consulting
- http://www.dedasys.com/
David J. (Guest)
on 2006-03-25 21:58
(Received via mailing list)
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
This topic is locked and can not be replied to.