InnoDB versus MyISAM

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

Moderator: General Moderators

user___
Forum Contributor
Posts: 297
Joined: Tue Dec 05, 2006 3:05 pm

InnoDB versus MyISAM

Post by user___ »

Hi guys,
I have read enough about InnoDB and MyISAM storage engine to choose in MySQL but I can not decide which one to choose yet. I am developing a large application that has a large database. I know that InnoDB performs better than MyISAM in Locking and Concurrency, Transactions, foreign keys, etc. while MyISAM is much faster, allows full-text search, etc. I need my database to be fast enough but InnoDB does not support full-text search and on the other side MyISAM does but locking in InnoDB is crucial.
My question is which of them would you reccomend me(I know that it depends on the application so I need to have an engine that is very fast and is secure enough because the application is intended to be visited by a lot of users).
If there is a better way for searching than full-text search I would be reallly happy to know about it.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Moved to Databases.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Is locking crucial to your application? You can use multiple storage engines in a database, MySQL doesn't really care. Many applications don't really need locking all that much.
user___
Forum Contributor
Posts: 297
Joined: Tue Dec 05, 2006 3:05 pm

Reply

Post by user___ »

I see your point I a totally agree but I have reab a book that says that if you have a lot of people visiting your portal locking is a must. So if we do not pay such an attention on locking is it the best choice for me to use MyISAM concerning this that I need really fast responses?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

If you need it highly responsive consider a dedicated database server or a database cluster. If your scale isn't that high for a while then MyISAM is fine for the most part. Also keep in mind the HEAP/MEMORY storage engine too.
user___
Forum Contributor
Posts: 297
Joined: Tue Dec 05, 2006 3:05 pm

Reply

Post by user___ »

Thank you.
User avatar
AKA Panama Jack
Forum Regular
Posts: 878
Joined: Mon Nov 14, 2005 4:21 pm

Post by AKA Panama Jack »

One thing to remember is that INNODB is very, very memory intensive as it loads entire tables into memory to work with the data. So if you have large tables you will be using megabytes of memory just to process queries on those tables. If you are using MYISAM then indexes are the only things stored in memory and the data is manipulated on the drive or the available cache.

INNODB is very fast but is a huge memory hog and some hosting companies that use shared database servers will get irritated and shut you down if your INNODB tables start using too much database server memory. So use INNODB for small tables and MYISAM for large tables. If you have your own dedicated database server then you can probably get away with using INNODB tables.
user___
Forum Contributor
Posts: 297
Joined: Tue Dec 05, 2006 3:05 pm

Reply

Post by user___ »

I totally agree with what you AKA Panama Jack said but is it a good practice to do the following:
This is just a simple example of what I was thinking about.
There are two tables;
One of them contains id|title|userid;
The other has id|news|date
It is obviuous that the first is a small one. It contains id(int), title(varchar(15)), and userid(int);
The other has id and date which are small too but there is the content column which is memory cosuming;

If the first table is MyISAM and the second InnoDB is not it be better than the opposite. In this way I think that when a search is performed on the first table and then by the id that had been got from that search a new search can be performed on the other table.

If I am wrong please correct me.
Last edited by user___ on Thu Feb 01, 2007 1:49 pm, edited 1 time in total.
User avatar
AKA Panama Jack
Forum Regular
Posts: 878
Joined: Mon Nov 14, 2005 4:21 pm

Post by AKA Panama Jack »

It all depends upon the situation.

Sometimes MYISAM is going to be faster than INNODB due to the way the indexing works. With MYISAM indexing is loaded into memory and all queries will parse the index looking for matches before doing a table scan. If you have a table with a million records and each record is only a few bytes then MYISAM can be faster than INNODB.

Personally I wouldn't only use INNODB if you absolutely need automatic row locking when the table is accessed. The vast majority of applications using databases do not need row locking so you should probably use MYISAM for every table.

You could give the user of your application the option of using either database type at the install phase. If they are running on their own dedicated database server then they can use INNODB. If they are on a shared host then they can use MYISAM. You don't have to make every table INNODB just the ones you think might benefit.
user___
Forum Contributor
Posts: 297
Joined: Tue Dec 05, 2006 3:05 pm

Reply

Post by user___ »

Thank you for your reply. I did get your point but you are arguing with yourself in a way:
...so you should probably use MYISAM for every table.
You don't have to make every table INNODB just the ones you think might benefit.
Which is better:Use only MyISAM or mix MyISAM with InnoDB according to the table.
User avatar
AKA Panama Jack
Forum Regular
Posts: 878
Joined: Mon Nov 14, 2005 4:21 pm

Post by AKA Panama Jack »

Nope, not arguing with myself at all. :)

Personally I don't use INNODB tables anymore but some people think they are needed. With all of my testing while developing ADOdb Lite I didn't find any real benefit from using INNODB tables except for the automatic row locking.
user___
Forum Contributor
Posts: 297
Joined: Tue Dec 05, 2006 3:05 pm

Reply

Post by user___ »

I see your point. I also think that MyISAM is better than InnoDB but I have read that its ability to create transactions is crucial(I know that it is crucial with Systems that serve banks but I am not sure where you should use transactions in a Web Application. Maybe in E-Shopping).

On the other hand there is something else which must be taken into consideration about Locking(As you have said) and transactions. I have read an article about this stuff and the way that was described to cope with Locking with MyISAM was to create a lot of additional tables(Each table that can be deleted from should have an additional one according to them). They say that if you have a table with (Let say topics) then if you want to delete a topic from it you need to insert its id in another table(That contains only ids that have been deleted so far) and then by Join to extract only these topics whose ids do not belong to the table that contains the deleted ids.

So I wonder if for example we have the same case as the above one and do not create the table containing ids, that were deleted, will it be a problem for the application and do we have to follow such(I say not the same but such) a way when creatung tables.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Locking and transactions assume the inserting, updating and/or deleting of mounds of data under massive amounts of traffic. Does your application need that?
user___
Forum Contributor
Posts: 297
Joined: Tue Dec 05, 2006 3:05 pm

Reply

Post by user___ »

The traffic is high but the
inserting, updating and/or deleting
are very often but not as much as selectings are.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Is it catastrophic for a selection to receive older data (that hasn't been updated yet)?
Post Reply