Optimizing MySQL Query inCI

Question

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.

So should I run CREATE INDEX on the table and then run the above code ? — noob
– noob, Commented Apr 1, 2014 at 6:29
Yes, the index will stay on the table. It is independent of the PHP system. — bitWorking
– bitWorking, Commented Apr 1, 2014 at 6:32
yas @noob add indexes on table . same issue i face link codereview.stackexchange.com/questions/43287/… — Haseeb
– Haseeb, Commented Apr 1, 2014 at 6:35
One more thing, After indexing update and delete queries won't change , right? — noob
– noob, Commented Apr 1, 2014 at 8:01

Evgeny · Accepted Answer · 2014-04-01 11:21:51Z

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";
}

Collectives™ on Stack Overflow

Optimizing MySQL Query inCI

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related