I have created a view like gmail inbox where inbox is clickable from left side and then there are tabs on top , like Promotions, Forums etc. When user will click on each tab, then the set of items that belong to that tab is shown, for example - forums and MySQL query is fired.
The individual views can have little differences in items look and feel but navigation is alike.
So, there are individual SQL queries being fired for each tab on click, but now I have to also show count of total items for each tab all the time and keep it updated upon click of any other tab / left main icon as well.
So, I am ending up firing SQL queries for all tabs and keep their counts all the time which is very database intensive.
The other extreme will be to keep all such counts in MySQL table and increment/decrement it all the time for which I need to change all existing functionality. Please suggest other means for such navigations.
PHP Yii framework 1.1.* and MySQL 5.6.
The way you're loading and counting the items for each tab can make a difference to the MySQL load and the page speed. A simple benchmark between CActiveRecord::count() and CActiveRecord::findAll() can be seen here:
$modelCount = Model::model()->count();
$modelCount = count(Model::model()->findAll());
// DB profile log:
// system.db.CDbCommand.query(SELECT COUNT(*) FROM `throw` `t`) 1 0.00029s
// system.db.CDbCommand.query(SELECT * FROM `throw` `t`) 1 0.00283s
The CActiveRecord::count() is ten times faster, even before you've counted the FindAll() with PHP count(). Of course, for the tab you're actually on - you could use the same findAll() query to count for the tab number, and populate the list of items on that page.
To test this for yourself and experiment with SQL query speeds, add query profiling add the following to your config.php file:
'db'=>array(
// ... existing db config etc
'enableProfiling' => true,
'enableParamLogging' => true,
),
And in log config...
'log'=>array(
'class'=>'CLogRouter',
'routes'=>array(
array(
'class' => 'CProfileLogRoute',
'levels' => 'profile',
'enabled' => true,
),
Optimising up your count queries might be enough to improve your page speed and MySQL load to your or your user's satisfaction.
Alternatively you could keep the tab counts in a session array:
Yii::app()->session['tabCounts'] = array(
'tab1' => 44,
'tab2' => 456,
'tab3' => 9345,
);
then in any controller action that would result in a change to the number of items, add a hook to alter the number. Of course you'd still have to make the initial MySQL queries to populate the array on first page load.