Previous website did not use framework and work with database giant and the total size approximately 30G with 40 million rows of all tables. In particular, the largest table have 12 million rows. i’m using MyISAM.
This time, I want to rebuild the source YiiFramework. I look Yii support very good ActiveRecord relation with InnoDB. But Innodb not good with big table rows when query offset limit.
With MyISAM tables I was forced to split into many different tables.
The table contains the fields of large tables. and relate to each other through the primary key.
This is very good in cases with foreignKey of Innodb.
I need to consult experts before make building the system.
It’s difficult to say what is best… I suggest you to read about the difference between InnoDB and MyIssam and depending on your database use to select the one that is best for your needs…
Just one example:
On MyISAM… mysql uses table locking for updates. Not for INSERT but… each time any record is updated it locks the entire table… so if you have many visitors on your site that are updating something (a forum for example)… those locks can quickly queue up.
This would not happen with InnoDB as there row locking is used.
If on the other hand… if the update is allowed only to few users (admin) and the general visitors only reads the database (SELECT) than MyIsam is much faster… but there you don’t have foreign keys - not a problem as you can just issue another select to get the related data…