Page 1 of 1

MySQL storage engines

Posted: Sun Mar 08, 2009 4:39 pm
by Luke
I generally use the innoDB storage engine for mysql because I want referential integrity. Every once in a while though, I need full-text intexing and I'm forced to use MyISAM. Is there a way to maybe get the best of both worlds? I hate having referential integrety "mostly". That sucks. I was thinking maybe of creating an innoDB table for everything BUT the full-text field, but that really doesn't solve the problem. What do you guys do?

Re: MySQL storage engines

Posted: Sun Mar 08, 2009 5:17 pm
by Luke
Forgive my ignorance, I'm pretty new to views, but could I possible create a view of this table that is stored in MyISAM? I'd imagine it isn't possible since I don't think views actually use a new table. They just use a subset of an existing table, right?

Re: MySQL storage engines

Posted: Sun Mar 08, 2009 6:02 pm
by Bill H
They just use a subset of an existing table, right?
As I understand them, yes.

Re: MySQL storage engines

Posted: Sun Mar 08, 2009 7:03 pm
by califdon
I think so, too, but there are other differences between MyISAM and InnoDB and they might even be more important, at least for some applications. I'm definitely no expert in MySQL internals, but I've attended a couple of tutorials that dealt with this (too bad my memory is so bad!) and it has a lot to do with how the data is stored internally, with relation to the primary index. In effect, MyISAM indexes are separate files, even for the primary index, while InnoDB stores the data itself with the primary index in the same file, so where a MyISAM lookup by primary key just gets a pointer which requires another fetch to get the data from another file, an InnoDB lookup by primary key gets the data on the first index fetch, making it faster in reading large tables. On the other hand, InnoDB is slower writing new records because it has to re-hash the indexes, or something (I'm not really that good at this stuff!). Anyway, my point is that there are other considerations besides referential integrity in choosing which engine to use. However, for a particular application, especially if it's not a humongous database or getting thousands of hits per second, referential integrity might well be the right characteristic to use as a criterion.

Re: MySQL storage engines

Posted: Sun Mar 08, 2009 7:11 pm
by Luke
Yea, I am not an expert either, but you're absolutely right. I really prefer PostgreSQL ever since I started working with Django. I don't like having to choose between storage engines. It is a pain in the ass. I really don't like just MOSTLY having referential integrity because I might as well have none at all. This annoys me. See, I also need transactions and as far as I know MyISAM doesn't support those either. InnoDB is definitely what I need, but there are a few tables where I need full-text intexing and InnoDB doesn't offer that. :( I miss PostgreSQL :(

Re: MySQL storage engines

Posted: Sun Mar 08, 2009 7:15 pm
by Luke
You know what? I'm switching to PostgreSQL. Problem solved. :)

Re: MySQL storage engines

Posted: Sun Mar 08, 2009 7:25 pm
by VladSun
Luke wrote:You know what? I'm switching to PostgreSQL. Problem solved. :)
I would vote for that too ;)