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…
You already know how myisam behaves from your current/old project.
Give innodb a try, i tell you, you will be happy with the choice.
But be aware, innodb requires extra knowledges, not only on the relational database design side, but also, in configuration of the storage engine to get the best out of it.
My site as community have many visitors. Members users small blog, shoutbox, comments … and manager posts, notes,upload media…
Example: Modules Article have many tables
[list=1]
[*]articles
[*]articles_info
[*]articles_body
[*]articles_meta
[*]articles_tags
[*]articles_author
[*]articles_categories
[*]articles_comments
[*]articles_words (keywords in title and content of the article)
[*]articles_words2article (relation to find article via keywords on articles_words)
[]articles_**
[]**_***
[/list]
with MyISAM select (left|right)join table with articles_***.key_primary = article_id to get full infos articles item.
because size 30G so i must split to many tables and can’t use full-text search (match against). if use full-text query time longer than 1 minute and busy server.
And i have using memcached to cache $variables and output html.
Website current working good.
if using old mysql commands for yii arise some problems when using model (one {{table}} only), example select left join and select from multiple tables
If using ActiveRecordRalations will easily than more
