CODEWITHSUNDEEP
phpmysqlsqlcodeigniter
noob
noob

Reputation: 641

Optimizing MySQL Query inCI

I am using Codeigniter + MySQL. I want to print the number of records against each category in a section. I have an ads table, category table and section table. This is my code : 

foreach($categories as $category){
$query = $this->db->query(" SELECT count(*) AS total_ads from ads where categoryid = $category->id and ( status = 1 OR status = 3 ) and sectionid = $section->id");
$count_row = $query->row();
$count  = $count_row->total_ads;
}

Total ads is around 62138 (records). But its taking too much time for the server to respond (around 4 secs). Is there something that can speed up this code, I mean changes to optimize.

Upvotes: 0

Views: 146

Answers (1)

Evgeny
Evgeny

Reputation: 102

Try to make new index in DB:

CREATE INDEX cat_sec_status ON ads (categoryid, sectionid, status);

And change you code to this:

$query = $this->db->query("
    SELECT COUNT(id) AS total_ads, categoryid
    FROM ads
    WHERE categoryid IN $categorysIds
        AND sectionid = $section->id            
        AND status IN (1,3)
    GROUP BY categoryid
");

$count_row = $query->row();

In variable $categorysIds store all categoties IDs in format '(1,2,3,4,5)'. You don`t need foreach with this code.

UPDATE: The final code may be this:

// Extract categories Ids into array
$categoriesIds = array_map(function($object) {return $object->id;}, $categories);

// prepare array to SQL statement
$categoriesIdsCondition = '(' . implode(',', $catIds) . ')';

// 'Good' statuses
$enabledStatuses = array(1, 3); 
$enabledStatusesCondition = '(' . implode(',', $enabledStatuses) . ')';

// Get records count of every category
$query = $this->db->query("
    SELECT COUNT(id) AS total_ads, categoryid
    FROM ads
    WHERE categoryid IN $categoriesIdsCondition
        AND sectionid = $section->id            
        AND status IN $enabledStatusesCondition
    GROUP BY categoryid
");

// If there is any records, store them
if ($query->num_rows() > 0)
{
   $adsCounter = $query->result();
}

// Usage example
foreach($adsCounter as $categoryAds){
    echo "In the category with ID = 
        . {$categoryAds->categoryid}
        . there are
        . {$recordsCounter->total_ads}
        . records";
}

Upvotes: 1

Related Questions