How Query is cached and why COUNT(*) not COUNT(1) ?

vorapoap

I use a debugger module to find out how my own plugin query DB.

First of all.. I use something like this

    $SMSListModel = new Gdn_Model('sms_history');
    if (!$UserID) return $SMSListModel->SQL->GetCount('table_user_sent_sms');
    else return $SMSListModel->SQL->Where('user_id = ', $UserID)->GetCount('table_user_sent_sms');

The debugger report COUNT(*) is used for this GetCount... for performance wise.. I heard that we are recommended to use COUNT(1) ?

Secondly, I try to run this.

$SMSListModel = new Gdn_Model('sms_history');
$r = $SMSListModel->SQL->Select('given_credit','SUM')->From('table_user_credit_record')->Get();
var_export($r);

Var_Export always return something like this Gdn_DataSet::__set_state(array( 'Connection' => PDO::__set_state(array( )), '_Cursor' => -1, '_DatasetType' => 'object', '_EOF' => false, '_PDOStatement' => PDOStatement::__set_state(array( 'queryString' => 'select SUM(given_credit) as given_credit from GDN_table_user_credit_record table_user_credit_record', )), '_Result' => NULL, ))

However if I run SELECT SUM(given_credit) FROM table_user_credit_record... there should be something returning but not NULL..
I wonder if Vanilla catch DB query somewhere?.. or whether my above SQL is incorrectly built? (The debugger module reports it correctly though)

businessdad

@vorapoap said:
The debugger report COUNT(*) is used for this GetCount... for performance wise.. I heard that we are recommended to use COUNT(1) ?

There is an interesting discussion on StackOverflow about the pros and cons of COUNT(1) on MySQL: COUNT(*) vs. COUNT(1) vs. COUNT(pk): which is better?. Just keep in mind that the recommendations might not apply to other RDBMS.

Var_Export always return something like this Gdn_DataSet::__set_state(array( 'Connection' => PDO::__set_state(array( )), '_Cursor' => -1, '_DatasetType' => 'object', '_EOF' => false, '_PDOStatement' => PDOStatement::__set_state(array( 'queryString' => 'select SUM(given_credit) as given_credit from GDN_table_user_credit_record table_user_credit_record', )), '_Result' => NULL, ))

However if I run SELECT SUM(given_credit) FROM table_user_credit_record... there should be something returning but not NULL..
I wonder if Vanilla catch DB query somewhere?.. or whether my above SQL is incorrectly built? (The debugger module reports it correctly though)

That is correct, the result is a Gdn_DataSet. To get the actual data, you have to use $r->Result(), $r->ResultArray() and loop through the returned value. For bigger datasets, it's not recommended to use the above methods, as they return all the data at once. In such case, you can implement your own loop, using $r->FetchFirst() and $r->FetchNext().

vorapoap

In addition to above (second) question..
I just realized I don't need model in this case... but running

$r = Gdn::SQL()->Select('given_credit','SUM')->From('table_user_credit_record')->Get();
    var_export($r);

..result the same

businessdad

@vorapoap said:
The debugger report COUNT(*) is used for this GetCount... for performance wise.. I heard that we are recommended to use COUNT(1) ?

There is an interesting discussion on StackOverflow about the pros and cons of COUNT(1) on MySQL: COUNT(*) vs. COUNT(1) vs. COUNT(pk): which is better?. Just keep in mind that the recommendations might not apply to other RDBMS.

Var_Export always return something like this Gdn_DataSet::__set_state(array( 'Connection' => PDO::__set_state(array( )), '_Cursor' => -1, '_DatasetType' => 'object', '_EOF' => false, '_PDOStatement' => PDOStatement::__set_state(array( 'queryString' => 'select SUM(given_credit) as given_credit from GDN_table_user_credit_record table_user_credit_record', )), '_Result' => NULL, ))

However if I run SELECT SUM(given_credit) FROM table_user_credit_record... there should be something returning but not NULL..
I wonder if Vanilla catch DB query somewhere?.. or whether my above SQL is incorrectly built? (The debugger module reports it correctly though)

That is correct, the result is a Gdn_DataSet. To get the actual data, you have to use $r->Result(), $r->ResultArray() and loop through the returned value. For bigger datasets, it's not recommended to use the above methods, as they return all the data at once. In such case, you can implement your own loop, using $r->FetchFirst() and $r->FetchNext().

vorapoap

Thanks...
Hmm.. I just used this and it works instantly..

return GetValue("Credit", Gdn::Database()->Query(sprintf("SELECT SUM(given_credit) AS Credit FROM GDN_table_user_credit_record "))->FirstRow(DATASET_TYPE_ARRAY),0);

I guess , I will do a cache.. using Gdn_Cache..

vorapoap

@businessdad Thanks for interesting on COUNT(1) VS COUNT(*) ..

For now, could someone quickly explain to me how to store above value for each page view use and re-query it every 1 hour.. ?... I saw many ways of using cache in the source code which is confusing to me.

In the future, I am very interested to utilize APC for storing long term value like configuration, but I will need to get through this gate of hell first. haha