Please upgrade here. These earlier versions are no longer being updated and have security issues.
HackerOne users: Testing against this community violates our program's Terms of Service and will result in your bounty being denied.

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

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)

Tagged:

Best Answer

Answers

  • 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

  • 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..

  • @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

Sign In or Register to comment.