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: -----

#21 User is offline   Psih 

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

Posted 05 January 2012 - 04:39 AM

View PostDa:Sourcerer, on 16 November 2011 - 10:15 AM, said:

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.

I know, but right now I'm fully InnoDB and don't have suitable work for Archive or MyISAM. I just had quite a time with MyISAM on one heavy loaded site with tons of reads and writes with a few extremely hot tables (50/50 reads/writes) peaking at 1500 SQL queries/sec. MyISAM broke every few days and I had to do optimize with repair 2 times a day to keep it working - still got some instances of corruption to handle at peak time. So I invested time into converting everything into InnoDB and some query rewriting for it's specifics - never had a DB issue related to data corruption since for 4 years now.

If someone doesn't want to stick with the InnoDB - he should look at the MariaDB - the successor of MyISAM. Personally never had a chance to work with it, but it's redesigned to be fault-resistant and doesn't corrupt data randomly like MyISAM can - at least people never complained about it.
0

#22 User is offline   Da:Sourcerer 

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

Posted 05 January 2012 - 05:35 AM

Huh? I think you're confusing a few things there: First off, I didn't make a stand for MyISAM. I just pointed out that tables managed by the Archive storage engine are more suitable for logs.

Then, MaraiaDB is a community fork of the MySQL RDBMS which has been brought into life after the acquisition of Sun Microsystems (who also owned the rights on MySQL) by Oracle and includes (among other things) some modifications by Percona. InnoDB, MyISAM and Archive are storage engines supported by both.

The "successor" of MyISAM in MariaDB is AriaDB. Likewise has InnoDB been replaced by XtraDB. For compatibility reasons, you can still invoke them by their old names.

If you want my personal impression on MaraiaDB: It's great! MaraiaDB brings some fantastic extensions such as the subquery optimizer. By rigerous fine-tuning, I managed to cut memory consumption on one of my deployment systems by a third. However, there are enough changes to the codebase now that there are queries that only work on one of them. E.g. MariaDB refuses to store broken dates in DATETIME fields. While this is actually a good thing, some applications rely on that (I think WordPress does), making MariaDB unsuitable for general purpose hosting. However, as a rule of thumb, every query that works on MySQL should work on MariaDB as well. So: Develop on MySQL, test and deploy on MariaDB.
programmer /ˈprəʊgramə/, noun: a device that converts ►coffee into ►code
0

#23 User is offline   fantgeass 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 22
  • Joined: 25-February 11

Posted 25 January 2012 - 05:22 AM

InnoDB, because MyISAM is obsolete.
42
0

#24 User is offline   Mike Chojnacki 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 15
  • Joined: 31-January 12

Posted 06 February 2012 - 05:15 AM

It really does depend, I prefer innodb when I know there will be some complicated relationships and the use of foreign keys is required, and I can't really think of a case where the old myisam would be preffered.
0

#25 User is offline   jacmoe 

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

Posted 06 February 2012 - 12:52 PM

I usually try to use Innodb, but I regret doing it.. because sometimes you end up on a host with only MyISAM, and everything goes to Hell. Posted Image
"Less noise - more signal"
1

#26 User is offline   ekerazha 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 525
  • Joined: 10-October 08
  • Location:European Union

Posted 06 February 2012 - 02:46 PM

InnoDB, what else
Yii user #37
0

#27 User is offline   bglee 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 152
  • Joined: 21-November 10
  • Location:Oak Park, IL

Posted 06 February 2012 - 10:38 PM

Another nod to InnoDB. I admit to being biased. I have a long and sordid history as an Oracle (and other) DBA. Anything without foreign keys is to be sneered at. :D
It takes 2 points to draw a straight line, but at least 3 to draw a conclusion.
0

#28 User is offline   jacmoe 

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

Posted 07 February 2012 - 09:10 AM

Yes, but how do you deal with the situation where it's not available?

I tend to use it, but then it bites me when it's not possible to use it.
I still need to look into this, so that my app will function properly under MyISAM.
That would probably require additional checks and injected code?
"Less noise - more signal"
0

#29 User is offline   Ben 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 267
  • Joined: 15-March 09

Posted 07 February 2012 - 04:41 PM

Never tested it, but for SQLite, there were programs that created triggers to simulate foreign key capabilities (for example this one). Maybe the same aproach could be used with MyISAM tables if your application depends on referential integrity.
Don't like ads in my sig...
1

#30 User is offline   antonywu 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 5
  • Joined: 23-April 11

Posted 13 April 2012 - 07:09 PM

Does Yii framework natively support MariaDB?
0

#31 User is offline   Ivica 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 317
  • Joined: 25-May 11

Posted 14 April 2012 - 01:48 AM

I also think that InnoDB is better, but it might depend on specific project/table. For example InnoDB table doesn't support fulltext search, so in that case you either need to switch to MyIsam table, or stick with InnoDB table, and find some otother solution.
Zurmo: Yii Powered Open Source CRM
zurmo.org
0

#32 User is offline   Da:Sourcerer 

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

Posted 14 April 2012 - 03:21 AM

@antonywu
MariaDB is a drop-in replacement. there is no need for a native support. Unless you're doing something really fancy with your queries, it will just work ;)

@Ivica
Actually, InnoDB has got fts support too since MySQL v5.6.x. It's still better to count on a solution like Sphinx or Apache Lucene.
programmer /ˈprəʊgramə/, noun: a device that converts ►coffee into ►code
0

#33 User is offline   rapidtransit440 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 27
  • Joined: 29-March 10

Posted 16 April 2012 - 05:58 PM

I haven't used MySQL for awhile, I had a piece of software that only ran with Postgres and I've never looked back.

InnoDB has a similar feature set and is the lesser of two evils ;D
0

#34 User is offline   TBIKC 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 14
  • Joined: 05-April 11

Posted 28 May 2012 - 04:57 AM

MyISAM already out of date
0

#35 User is offline   mahi 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 12
  • Joined: 14-January 12

Posted 23 October 2012 - 06:47 AM

I prefer InnoDB as it allows to use cascade that keeps integrity and for maintaining security of database. I messed up with on of project using MyISAM.
EIPIYRWITDS : Everything is possible if you really want it to do so.
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