[Trejder]

Hi there,

As a continuity to this post. If someone have some spare time, please share with me (and others) your feelings about using InnoDB and MyISAM storage engines in MySQL? Dose having (or not) support for transactions (as in mentioned post) is the only difference? Which one is faster or serves better for Yii, in your opinion? What are factors that Yii developer should take into account, when selecting storage engine for MySQL DBMS?

Thanks in advance for any response!

[jacmoe]

I prefer InnoDb because I like that it supports foreign keys and thus saves me time and effort by firing triggers for me upon update/delete.
Referential integrity should IMO be handled by the database.
But, as I found out a small week ago, not all hosts (my new blog as a matter of fact) does not support InnoDb..
So I need to write all that jazz code myself (and I hate that): delete all dependent stuff when a post is deleted..
I know that we shouldn't rely on the database taking care of business, but that's what it's there for, isn't it?

I know that people stay that MyISAM is faster, but I would prefer InnoDb any day.

Maybe because I don't know better.

I'd love to hear what others (and more experienced people) have to say.

[Trejder]

jacmoe, on 13 December 2010 - 06:32 PM, said:

I know that we shouldn't rely on the database taking care of business, but that's what it's there for, isn't it?

Well, I'm for sure not more experienced developer, but I agree with those other, you cite here. I also heard many different, not related to each other (or particular project) people saying that lending business logic partially or in whole to database is not too wise idea, as in some way you are using control over it. Me personally, even if I would decide to InnoDB I would still stay on "older" MyISAM-like approach, where all the logic is handled by Yii, in methods like beforeSave for example.

[Mike]

jacmoe, on 13 December 2010 - 06:32 PM, said:

I know that we shouldn't rely on the database taking care of business, but that's what it's there for, isn't it?

I see it exactly the other way round: A perfect DBMS should be a system that ensures that our data is always in a consistent state. So if our code has a bug, it still should be impossible to leave the DB in an inconsistent state. If you think that's bad, then consider the fact, that a huge part of business rules is already defined in DB: The column data types are the basis for our validation rules. In the same way the DB will never allow to save a string to a INT column, it should not allow to e.g. add a record with a non existant foreign key id.

[Maurizio Domba Cerin]

I agree with Mike... as we are using a MVC standard and respecting the logic for what goes in a model/view/controller... the same is for the database logic that has it's place in the database...

[Trejder]

mdomba, on 14 December 2010 - 04:48 AM, said:

I agree with Mike... as we are using a MVC standard and respecting the logic for what goes in a model/view/controller... the same is for the database logic that has it's place in the database...

Then, what is use of methods like beforeSave()? :]

Plus, I met two or three situations in a different company where it was stated that even best developer will never be the best SQL server admin (and vice versa). If following this thinking, placing business logic in DB produces additional time / problems on cooperation point.

But, this is quite of topic. I agree with you. You made me change my mind. Thank you for all your replies.

[Maurizio Domba Cerin]

beforeSave() can be used to add some values that need to be saved like for example timestamp (could not think of best example right now)... or to change the format to be saved (date fields)...

Anyway in the best scenario you have a good database developer and a good programmer that works together

[Bala]

mdomba, on 14 December 2010 - 08:28 AM, said:

beforeSave() can be used to add some values that need to be saved like for example timestamp (could not think of best example right now)... or to change the format to be saved (date fields)...

Anyway in the best scenario you have a good database developer and a good programmer that works together

There are many uses to the before/afterSave() methods aside from data validation. As far as possible constraints should be set up in the database, every web developer needs to have a good grasp of at least basic database management. Check out these interesting articles on the subject:
http://blogs.sitepoi...table-pros-con/
http://blogs.sitepoi...-php-emulation/

[Trejder]

Bala, on 16 December 2010 - 01:12 PM, said:

http://blogs.sitepoi...table-pros-con/
http://blogs.sitepoi...-php-emulation/

Hm... strange (is it?)... I'm getting error saying that Firefox is unable to locate blogs.sitepoint.com server. Tested on 2010-12-16, 23:40 GMT+1.

[Antonio Ramirez]

I do use InnoDB because I tend to use transactions, cascade operations and a lot of data manipulation, if I just store some data and it is mostly accessed than manipulated, then I choose MyISAM.

Nevertheless, and apart from Rent a car systems, I never developed bigger systems than that, which required more DB performance considerations.

Once I was working in Singapure for www.sprice.com asia. I developed the front end interface experience (javascript) for flights and hotels but our DB was not mysql. So I never had the chance to evaluate its efficiency in huge systems.

[Mike]

I've also entered some magic keywords ('myisam innodb benchmark') into my secret knowledgebase called GOOGLE ...

[Trejder]

Mike, on 17 December 2010 - 04:07 AM, said:

I've also entered some magic keywords ('myisam innodb benchmark') into my secret knowledgebase called GOOGLE ...

Mike! That is a wonderful idea! :] I would never figured it out, if you wouldn't tell me the secret! :}

Seriously! I started this post not to take any numbers or benchmarks because - as you correctly assumed - this can be done via Google. I wanted to gather some other developers opinions and ideas on this subject, as such response may be (and many times - is) much, much more valuable than numbers.

Take my beloved (yeak! :/) Oracle as an example. If you dig through whole bunch of results, benchmarks and numbers you may made yourself thinking that if properly configured, run on correctly setup hardware and managed by an experienced administrator, this is the fastest RDBMS among all available now on the market (forgive me if my opinion is based on wrong assumptions, this is only an example). But even so, my personal coding practice in Yii is that this is the worst RDBMS to be choose for Yii development and I wouldn't either use it or advice someone else to use it, if I wouldn't be forced to do so, even if someone would gave me licensee for it for free.

[Antonio Ramirez]

Hahaha... Mike doesn't extend much its answers but always get to the point. Nevertheless, I also like personal experiences from people I start to network around a common interest

[Mike]

That's perfectly legal. But Google definitely has a larger user base for (not directly yii related) questions like these.

And Trejder: benchmark !== numbers

[itpg]

I prefere to use Myisam, because I want to define relation logic in code, not database, for example in some projects there is some cases when deleting base table record, I don't want automatically delete related table rows. Also myisam is faster for accesing data.

[Trejder]

itpg, on 30 October 2011 - 04:27 AM, said:

I prefere to use Myisam, because I want to define relation logic in code, not database, for example in some projects there is some cases when deleting base table record, I don't want automatically delete related table rows. Also myisam is faster for accesing data.

Thanks for your vote.

As I stated (or not?) I'm rather DB newbie. So, I'm just a little bit surprised, of what you've just wrote. I thought that an ability of defining relation logic directly in database (InnoDB) should speed up data access in comparison to situation, where such logic is defined on code side. Especially for a large datasets, with many relations. Am I wrong?

[Maurizio Domba Cerin]

Defining relations in database is not about getting faster the data... it's about data integrity and security.. because the database will not allow you to add a FK that does not exist... or delete a record if it is needed by the parent...

[befi]

I don't think InnoDb or MyIsam should be a choice of personal preference. Nor is one superior over the other.

It depends on the project's requirements. If you have lots of insertions and updates and/or you need data integrity, you should probably go with InnoDb as it supports transactions and all sorts of other neat stuff. MyIsam can get quite slow for these operations as it needs to update indexes each time.

If your project requires only a few updates and inserts, but lots of select operations (e.g. a heavy traffic website could be such a scenario: Let's say 1 blog post per day and a bunch of comments, but hundreds of thousands of visitors) you might want to think about MyIsam. Since the indexes are not updated very often, there's no penalty there but the read operations will be fast.

On the other hand, you can also cache stuff to minimize database access making InnoDb also interesting for these kind of projects.

[Psih]

I'd had to comment.

InnoDB is a default engine in MySQL as of 5.5 (or even earlier). Why? Because MyISAM is outdated, it's not being developed and if something goes wrong most of the MyISAM tables end in messed up state - it's just not fault tolerant. I'm not even mentioning that table can corrupt during the normal working process.

By all means, InnoDB is really superior. The only task MyISAM is good for is for storing logs witch are not important - write speeds are better (no transactions, isolation levels and foreign keys to process) and you can compress those tables.

[Da:Sourcerer]

Psih, on 16 November 2011 - 10:09 AM, said:

The only task MyISAM is good for is for storing logs witch are not important - write speeds are better (no transactions, isolation levels and foreign keys to process) and you can compress those tables.

Erm, I beg to differ. If that is really your use case, you might want to have a look at the Archive storage engine. MyISAM tables can be compressed, yes. But if you do so, you can no longer write to them.