Fixing mooSocial Slow Feed Loading: MySQL Query Optimization & Redis Caching

The mooSocial feed feels instant on a fresh install. Then you cross 5,000 users and 100,000+ activity records, and the home feed starts taking 4-8 seconds to render. LiteSpeed is fine, PHP is fine, but the page just hangs on the database. That hang is almost always the feeds table doing full table scans because mooSocial ships without proper composite indexes for the privacy-filtered timeline query.

Here's how to find the exact query killing you, index it correctly, and put Redis in front of the feed so repeat loads never touch MySQL at all.

Quick Diagnostic Cheat-Sheet

Step 1: Confirm the bottleneck with the slow query log

Don't guess. Turn on the slow query log and let it capture real traffic for a few minutes.

-- In MySQL as root
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL slow_query_log_file = '/var/log/mysql/moosocial-slow.log';

Now hit the site, reload the feed a dozen times, then read what surfaced:

mysqldumpslow -s t -t 10 /var/log/mysql/moosocial-slow.log

On nearly every busy mooSocial install the top offender looks like the timeline pull joining feeds, feed_users, and users while filtering on privacy and ordering by created.

Step 2: Run EXPLAIN on the feed query

Grab the raw query from the log and prefix it with EXPLAIN. A typical result before tuning:

EXPLAIN SELECT f.* FROM feeds f
WHERE f.privacy IN (0,1)
  AND f.is_removed = 0
ORDER BY f.created DESC
LIMIT 20;

+----+-------+------+---------------+------+---------+------+--------+-----------------------------+
| id | table | type | possible_keys | key  | key_len | ref  | rows   | Extra                       |
+----+-------+------+---------------+------+---------+------+--------+-----------------------------+
|  1 | f     | ALL  | NULL          | NULL | NULL    | NULL | 214883 | Using where; Using filesort |
+----+-------+------+---------------+------+---------+------+--------+-----------------------------+

type: ALL plus Using filesort over 214k rows is the smoking gun. MySQL reads every row, filters it, then sorts the whole thing in memory or on disk for every single feed load.

Warning: Take a full database snapshot before touching indexes or schema. On cPanel use Backup Wizard; from CLI run mysqldump --single-transaction moosocial_db > /root/moosocial_$(date +%F).sql. Index creation locks tables on large InnoDB datasets and can stall the site for the duration.

Step 3: Add the composite index that actually gets used

The query filters on privacy and is_removed, then sorts by created. A composite index in that exact order lets MySQL satisfy both the WHERE and the ORDER BY without a filesort.

ALTER TABLE feeds
  ADD INDEX idx_feed_timeline (is_removed, privacy, created);

-- If feed_users drives the follow-based timeline, index the join key too
ALTER TABLE feed_users
  ADD INDEX idx_feeduser_lookup (user_id, feed_id);

Re-run the same EXPLAIN. You want to see the index picked up and the filesort gone:

+----+-------+-------+-------------------+-------------------+---------+------+------+-------------+
| id | table | type  | possible_keys     | key               | key_len | ref  | rows | Extra       |
+----+-------+-------+-------------------+-------------------+---------+------+------+-------------+
|  1 | f     | range | idx_feed_timeline | idx_feed_timeline | 6       | NULL |   20 | Using where |
+----+-------+-------+-------------------+-------------------+---------+------+------+-------------+

Rows examined dropped from 214,883 to 20. That is the whole game.

Step 4: Tune the InnoDB buffer pool

Indexes only help if they live in RAM. If your buffer pool is smaller than your hot dataset, MySQL keeps reading indexes off disk. Check current size:

SELECT @@innodb_buffer_pool_size / 1024 / 1024 AS mb;

On a VPS with dedicated memory, set the buffer pool to roughly 60-70% of available RAM in your MySQL config:

# /etc/my.cnf
[mysqld]
innodb_buffer_pool_size = 4G
innodb_buffer_pool_instances = 4
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT

Restart MySQL after editing. On our NVMe-backed nodes O_DIRECT plus fast storage means even cold index reads stay under a millisecond. If you're squeezing this out of shared resources and constantly hitting LVE memory caps, a Cloud VPS gives you the headroom to size the buffer pool properly.

Step 5: Put Redis in front of the feed

Indexing fixed the query. Redis stops you running it at all on repeat loads. mooSocial supports cache drivers through its configuration. First confirm Redis is alive:

redis-cli ping
# PONG

redis-cli info memory | grep used_memory_human

Install the PHP Redis extension if it's missing, then point mooSocial's cache config at it. Edit the framework cache config (path varies by build, typically under app/config or the admin caching panel):

<?php
return array(
    'default' => array(
        'engine'  => 'Redis',
        'server'  => '127.0.0.1',
        'port'    => 6379,
        'prefix'  => 'moo_',
        'duration'=> 300,
    ),
);

Set a sane TTL. A 5-minute cache on the public feed cuts MySQL load dramatically while keeping content fresh enough for a social timeline. Verify keys are landing:

redis-cli --scan --pattern 'moo_*' | head
redis-cli monitor   # watch live during a page load, then Ctrl+C

Warning: Never expose Redis to the network without requirepass and a bound loopback interface. In /etc/redis.conf keep bind 127.0.0.1 and set requirepass to a strong value. An open Redis instance is a well-known remote code execution vector.

Step 6: Verify under real load

Measure before and after with a simple benchmark against the feed endpoint:

ab -n 200 -c 10 -C "session_cookie=yourvalue" https://yourdomain.com/home

# Watch MySQL threads during the run
watch -n1 'mysqladmin processlist | grep -c feeds'

With indexes plus Redis you should see feed response times drop from multiple seconds to well under 300ms, and the MySQL process list should stay nearly empty during cached hits.

Frequently Asked Questions

Why does my mooSocial feed slow down only after months in production?

The feeds table grows constantly. On a small dataset a full table scan is fast enough to hide the missing index. Once row counts pass tens of thousands, that same scan becomes the dominant cost of every page load. Nothing changed in your code, only the table size.

Will adding indexes slow down posting new activity?

Each index adds a small write overhead on INSERT. The composite index described here targets three columns, so the cost is negligible compared to the massive read savings. Feeds are read far more often than written, so the trade heavily favors indexing.

Do I still need Redis if the indexes fixed my query speed?

Indexes make individual queries fast; Redis eliminates redundant queries entirely. Under concurrency, serving a cached feed from memory instead of re-running the query for every visitor is the difference between a node that handles 50 concurrent users and one that handles 500.

Keep the feed fast without the manual tuning

The pattern above works, but you shouldn't have to fight your infrastructure to run a social network. Hostiso stacks pair LiteSpeed Web Server with NVMe storage and a preconfigured Redis layer, so index reads stay in memory and cached feeds never touch disk. CloudLinux LVE isolation keeps a single busy timeline query from starving your other processes. If your mooSocial community is outgrowing shared limits, moving to a properly sized Cloud VPS or a Dedicated Server gives your InnoDB buffer pool the RAM it needs to keep every timeline instant.