Avg Rows
Calls/
Query Latenc Examine Issue
sec
y (ms) d
🚨 Very high
SELECT user_id, rows
2237.5 789,447
qbank_attempt_count, ... from 0.08 examined —
uttc
0 .60
likely a full
table scan
SELECT
id_fr_learning_center_detail, ... 1681.4 671,059. 🚨 Massive rows
0.03
from 6 34 examined
erp_fr_learning_center_detail
Another similar query on 1669.3 848,347
0.03 🚨 Even worse
erp_fr_learning_center_detail 3 .73
Why is it Slow?
Full table scan happening because:
No index on the columns in your WHERE condition
Or no LIMIT or pagination if you’re fetching too much data at once.
When a table has 700k+ records, a full scan takes time.
Each query is reading hundreds of thousands of rows when it probably only needs a few.
Key Issues & Root Causes:
1. Full table scans on large tables
The queries are pulling large numbers of rows — 700k to 850k rows per query
Avg latency over 1.6 to 2.2 seconds
Likely missing indexes on filtering columns
Possibly no LIMIT clause if not all records are actually needed.
2. No Index Support for Filtering or Sorting
High rows examined indicate either:
o No WHERE clause (selecting everything)
o OR the WHERE clause columns are not indexed properly.
Similar issue as before: EXPLAIN would likely show type = ALL and high row count.
3. Inefficient Data Access Patterns
Fetching such large result sets repeatedly can signal either:
o Poor query structure
o Or application layer logic that should be restructured (e.g. fetching too much
data, or not paginating / limiting results)
How to Fix
🔍 What to
🔴 Issue ⚠️Reason 🛠️How to Fix
Check
👉 Identify WHERE columns
Run EXPLAIN
and create composite indexes
High rows examined SELECT ...
Full table scan due on those columns.
(700k–850k) in uttc Check type (if
to missing indexes 👉 Example:
and ALL = full table
or inefficient CREATE INDEX
erp_fr_learning_center_d scan)
WHERE clause idx_course_subject ON
etail queries Check high rows
user_to_topic_count
value
(course_id, subject_id);
Large volume of 👉 Add LIMIT + OFFSET for
Review SELECT
data being pagination queries
High latency (1600– statements — is
scanned/returned 👉 Example:
2200 ms) all data really
without LIMIT or SELECT ... FROM ... WHERE ...
needed at once?
pagination LIMIT 100 OFFSET 0
👉 Create summary tables or
No pre-aggregated materialized views
Multiple heavy Identify
data or summary 👉 Run background jobs to
SUM/grouping queries repetitive
tableEach query update aggregates
without aggregation aggregation
scans 80k+ rows periodically
optimization patterns
per call 👉 Use those for reporting
queries
Check lock
👉 Optimize application logic to
Potential lock If used too duration and
reduce lock hold time
contention frequently or lock number of
👉 Avoid frequent GET_LOCK
(GET_LOCK) held too long sessions waiting
unless absolutely necessary
for the same lock
Review
Multiple queries 👉 Combine multiple
application-side
Repeated similar individually fetching aggregations into a single
code for loops
queries (N+1 pattern) similar aggregated query using GROUP BY or
triggering
data per request CASE statements
multiple queries
Example: Index DDL Suggestions
Before
SELECT * FROM user_to_topic_count WHERE course_id = ? AND subject_id = ?
Check: Is this missing index?
👉 Run:
EXPLAIN SELECT * FROM user_to_topic_count WHERE course_id = ? AND subject_id = ?
If type = ALL → BAD.
Fix:
CREATE INDEX idx_course_subject ON user_to_topic_count (course_id,
subject_id);
Before (no pagination):
SELECT * FROM erp_fr_learning_center_detail WHERE learning_center_id = ?
Fix:
SELECT * FROM erp_fr_learning_center_detail WHERE learning_center_id = ? LIMIT 100 OFFSET
0;
Before (repeated SUM queries):
SELECT SUM(quantity) FROM course_order_group_details WHERE city = 'X';
SELECT SUM(quantity) FROM course_order_group_details WHERE city = 'Y';
Fix: Combine:
SELECT city, SUM(quantity) FROM course_order_group_details GROUP BY city;
Summary:
Run EXPLAIN → find slow full table scans
Add proper composite indexes on frequently filtered columns
Use LIMIT/OFFSET in queries returning many rows
Replace repetitive aggregate queries with GROUP BY or summary tables
Optimize application logic causing N+1 query patterns