Yii Framework Forum: MySQL: InnoDB or MyISAM - what is better for Yii - Yii Framework Forum

Jump to content

  • (2 Pages)
  • +
  • 1
  • 2
  • You cannot start a new topic
  • You cannot reply to this topic

MySQL: InnoDB or MyISAM - what is better for Yii Rate Topic: -----

#1 User is offline   Trejder 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 1,193
  • Joined: 06-October 10
  • Location:Southern Poland

Posted 13 December 2010 - 04:23 PM

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!
Proud Cookbook author, though still learning powerful Yii! :] See my generic profile for more information. Cheers!
-4

#2 User is offline   jacmoe 

  • Elite Member
  • Yii
  • Group: Moderators
  • Posts: 2,601
  • Joined: 10-October 10
  • Location:Denmark

Posted 13 December 2010 - 06:32 PM

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.
"Less noise - more signal"
0

#3 User is offline   Trejder 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 1,193
  • Joined: 06-October 10
  • Location:Southern Poland

Posted 14 December 2010 - 03:42 AM

View Postjacmoe, 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.
Proud Cookbook author, though still learning powerful Yii! :] See my generic profile for more information. Cheers!
0

#4 User is offline   Mike 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 3,013
  • Joined: 06-October 08
  • Location:Upper Palatinate

Posted 14 December 2010 - 04:00 AM

View Postjacmoe, 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.
0

#5 User is offline   Maurizio Domba Cerin 

  • Yii - Yesss It Is !!!
  • Yii
  • Group: Yii Dev Team
  • Posts: 4,346
  • Joined: 12-October 09
  • Location:Croatia

Posted 14 December 2010 - 04:48 AM

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...
Find more about me.... btw. Do you know your WAN IP?
0

#6 User is offline   Trejder 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 1,193
  • Joined: 06-October 10
  • Location:Southern Poland

Posted 14 December 2010 - 08:19 AM

View Postmdomba, 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.
Proud Cookbook author, though still learning powerful Yii! :] See my generic profile for more information. Cheers!
0

#7 User is offline   Maurizio Domba Cerin 

  • Yii - Yesss It Is !!!
  • Yii
  • Group: Yii Dev Team
  • Posts: 4,346
  • Joined: 12-October 09
  • Location:Croatia

Posted 14 December 2010 - 08:28 AM

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 :)
Find more about me.... btw. Do you know your WAN IP?
0

#8 User is offline   Bala 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 6
  • Joined: 02-December 10

Posted 16 December 2010 - 01:12 PM

View Postmdomba, 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/
0

#9 User is offline   Trejder 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 1,193
  • Joined: 06-October 10
  • Location:Southern Poland

Posted 16 December 2010 - 05:47 PM

View PostBala, on 16 December 2010 - 01:12 PM, said:


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.
Proud Cookbook author, though still learning powerful Yii! :] See my generic profile for more information. Cheers!
0

#10 User is offline   Antonio Ramirez 

  • Elite Member
  • Yii
  • Group: Yii Dev Team
  • Posts: 1,448
  • Joined: 04-October 10

Posted 16 December 2010 - 06:06 PM

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. :(
¿How long would it take for you to understand that you own nothing in this world?

www.ramirezcobos.com
www.2amigos.us
www.github.com/tonydspaniard
www.github.com/2amigos


Posted Image
0

#11 User is offline   Mike 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 3,013
  • Joined: 06-October 08
  • Location:Upper Palatinate

Posted 17 December 2010 - 04:07 AM

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

#12 User is offline   Trejder 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 1,193
  • Joined: 06-October 10
  • Location:Southern Poland

Posted 17 December 2010 - 04:59 AM

View PostMike, 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.
Proud Cookbook author, though still learning powerful Yii! :] See my generic profile for more information. Cheers!
0

#13 User is offline   Antonio Ramirez 

  • Elite Member
  • Yii
  • Group: Yii Dev Team
  • Posts: 1,448
  • Joined: 04-October 10

Posted 17 December 2010 - 05:08 AM

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
¿How long would it take for you to understand that you own nothing in this world?

www.ramirezcobos.com
www.2amigos.us
www.github.com/tonydspaniard
www.github.com/2amigos


Posted Image
0

#14 User is offline   Mike 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 3,013
  • Joined: 06-October 08
  • Location:Upper Palatinate

Posted 17 December 2010 - 05:21 AM

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

And Trejder: benchmark !== numbers
0

#15 User is offline   itpg 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 43
  • Joined: 01-April 11

Posted 30 October 2011 - 04:27 AM

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.
0

#16 User is offline   Trejder 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 1,193
  • Joined: 06-October 10
  • Location:Southern Poland

Posted 30 October 2011 - 06:11 AM

View Postitpg, 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?
Proud Cookbook author, though still learning powerful Yii! :] See my generic profile for more information. Cheers!
0

#17 User is offline   Maurizio Domba Cerin 

  • Yii - Yesss It Is !!!
  • Yii
  • Group: Yii Dev Team
  • Posts: 4,346
  • Joined: 12-October 09
  • Location:Croatia

Posted 30 October 2011 - 06:51 AM

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...
Find more about me.... btw. Do you know your WAN IP?
0

#18 User is offline   befi 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 25
  • Joined: 10-March 11

Posted 15 November 2011 - 11:39 AM

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.
0

#19 User is offline   Psih 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 114
  • Joined: 30-June 10

Posted 16 November 2011 - 10:09 AM

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.
0

#20 User is offline   Da:Sourcerer 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 1,222
  • Joined: 30-March 11
  • Location:Berlin, Germany

Posted 16 November 2011 - 10:15 AM

View PostPsih, 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.
programmer /ˈprəʊgramə/, noun: a device that converts ►coffee into ►code
0

Share this topic:


  • (2 Pages)
  • +
  • 1
  • 2
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users