CodeIgniter网站首次加载时单查询接口加载过慢问题排查求助

Hey there, let's figure out why that first Ajax call is dragging its feet while other parts of your site are snappy, and how to fix it. The key clues here are that it's only slow on the first load and it's a simple SQL query—so this is almost certainly related to database caching, connection overhead, or missing indexes.

Let's Break Down the Possible Causes #

• First-time database connection overhead: CodeIgniter waits until the first query runs to establish a database connection. That initial handshake adds latency, which goes away on subsequent requests when the connection is reused.
• Missing database indexes: Your query filters on c_module, c_created_userID, c_type_pid, and c_type_name—if there's no index covering these fields, the database has to scan the entire table the first time it runs the query.
• No query caching: Without caching, the database has to re-run the same query every time, even if the data doesn't change often.

Step-by-Step Fixes #

1. Add a Targeted Database Index #

This is the most impactful fix for first-load speed. Create a composite index that covers all the fields your query filters on. Run this SQL in your database:

CREATE INDEX idx_category_system_user ON codeigniter_system_category_type (c_module, c_created_userID, c_type_pid, c_type_name);

This lets the database jump straight to the rows you need instead of scanning every row in the table—huge speedup for the first (and every) query.

2. Optimize the SQL Query #

Your current query uses UNION for two very similar select statements. You can simplify it to a single query using IN, which is more efficient. I also added query binding to protect against SQL injection (a critical security best practice):

function get_folder_category_id($uid){
    $q_s_type = "SELECT c_type_id,c_type_pid,c_type_name 
                 FROM `codeigniter_system_category_type` 
                 WHERE `c_type_pid` = 0 
                   AND `c_module` = 'System' 
                   AND `c_created_userID` = ? 
                   AND `c_type_name` IN ('Others', 'Shared')";
    
    return $this->db->query($q_s_type, [$uid])->result_array();
}

3. Enable Persistent Database Connections #

Persistent connections let CodeIgniter reuse database connections across requests, eliminating the overhead of establishing a new connection on the first load. Update your config/database.php:

$db['default']['persistent'] = TRUE;

Just note that if your site gets high traffic, you might need to adjust your database's max connection limit to avoid hitting caps.

4. Add User-Specific Query Caching #

Since the query results are user-specific (tied to $uid), you can cache the results per user so subsequent loads pull from cache instead of the database. First, make sure your cache driver is configured in config/config.php:

$config['cache_driver'] = 'file'; // Or use 'redis'/'memcached' if available

Then update your model function to use caching:

function get_folder_category_id($uid){
    $cache_key = "folder_categories_{$uid}";
    
    // Check if we already have a cached result
    if ($cached_result = $this->cache->get($cache_key)) {
        return $cached_result;
    }
    
    $q_s_type = "SELECT c_type_id,c_type_pid,c_type_name 
                 FROM `codeigniter_system_category_type` 
                 WHERE `c_type_pid` = 0 
                   AND `c_module` = 'System' 
                   AND `c_created_userID` = ? 
                   AND `c_type_name` IN ('Others', 'Shared')";
    
    $result = $this->db->query($q_s_type, [$uid])->result_array();
    
    // Cache the result for 1 hour (adjust the timeout based on how often your data changes)
    $this->cache->save($cache_key, $result, 3600);
    
    return $result;
}

This way, after the first load, the next request grabs the cached data instantly.

Test It Out #

Start with adding the index—you should see an immediate improvement in first-load speed. Then layer in the other fixes to make it even snappier.

内容的提问来源于stack exchange，提问作者Shailesh Prajapati