MySQL storage engines

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

MySQL storage engines

Post 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?
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Re: MySQL storage engines

Post 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?
User avatar
Bill H
DevNet Resident
Posts: 1136
Joined: Sat Jun 01, 2002 10:16 am
Location: San Diego CA
Contact:

Re: MySQL storage engines

Post by Bill H »

They just use a subset of an existing table, right?
As I understand them, yes.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: MySQL storage engines

Post 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.
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Re: MySQL storage engines

Post 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 :(
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Re: MySQL storage engines

Post by Luke »

You know what? I'm switching to PostgreSQL. Problem solved. :)
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: MySQL storage engines

Post by VladSun »

Luke wrote:You know what? I'm switching to PostgreSQL. Problem solved. :)
I would vote for that too ;)
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply