Page 1 of 2
InnoDB versus MyISAM
Posted: Mon Jan 29, 2007 4:21 pm
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.
Posted: Mon Jan 29, 2007 4:24 pm
by RobertGonzalez
Moved to Databases.
Posted: Mon Jan 29, 2007 4:30 pm
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.
Reply
Posted: Mon Jan 29, 2007 4:35 pm
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?
Posted: Mon Jan 29, 2007 4:46 pm
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.
Reply
Posted: Mon Jan 29, 2007 4:53 pm
by user___
Thank you.
Posted: Mon Jan 29, 2007 6:40 pm
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.
Reply
Posted: Tue Jan 30, 2007 10:14 am
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.
Posted: Tue Jan 30, 2007 3:07 pm
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.
Reply
Posted: Tue Jan 30, 2007 3:24 pm
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.
Posted: Tue Jan 30, 2007 3:43 pm
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.
Reply
Posted: Tue Jan 30, 2007 4:26 pm
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.
Posted: Tue Jan 30, 2007 4:51 pm
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?
Reply
Posted: Tue Jan 30, 2007 4:59 pm
by user___
The traffic is high but the
inserting, updating and/or deleting
are very often but not as much as selectings are.
Posted: Tue Jan 30, 2007 5:05 pm
by feyd
Is it catastrophic for a selection to receive older data (that hasn't been updated yet)?