I enabled the slow query log. After two days, there are over 1 million lines in the log file. I asked Grok to analyze it. This was its response:
Slow queries examine ~1M rows each for single POST_ID lookups.
Core issue: USE INDEX (indx_2) forces inefficient index; likely indx_2 does not cover WHERE conditions well or is not optimal for PRIMARY KEY lookup.
Fix:
Remove USE INDEX (indx_2) — let optimizer choose (probably PRIMARY on POST_ID).
Ensure POST_ID is PRIMARY KEY or has unique index.
Check EXPLAIN on query without forced index.
Add composite index on (POST_ID, POST_IS_APPROVED, USER_ID) if needed.
These 2-second single-post fetches cause CPU spikes when many concurrent.
