优化MySQL查询inCI-Java 学习之路

我正在使用Codeigniter MySQL . 我想打印一个部分中每个类别的记录数 . 我有一个广告表，类别表和节表 . 这是我的代码：

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

广告总数约为62138（记录） . 但它花了太多时间让服务器响应（大约4秒） . 有没有什么可以加速这段代码，我的意思是改进优化 .

1 回答

尝试在DB中创建新索引：

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

并将代码更改为：

$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();

在变量$ categorysIds中存储格式为'（1,2,3,4,5）'的所有类别ID . 你不需要foreach这个代码 .

UPDATE: 最终代码可能如下：

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

回复于 2024-05-02T20:11:25+08:00

优化MySQL查询inCI

1 回答

相关问题