MySQL Tuning, Indexing & Concurrency
5-8 Years (Senior)
Medium
Production Scenario
How do Generated (Virtual & Stored) Columns and Functional Indexes optimize queries in MySQL 8?
Real-World Scenario / Incident Description
Searching through a JSON column `details->>"$.customer_code"` was executing full table scans.
633 views
•
Updated 1 month ago
Functional Index on JSON Expressions
ALTER TABLE orders ADD INDEX idx_customer_code ((CAST(details->>'$.customer_code' AS CHAR(30))));
MySQL 8 allows indexing expressions directly without needing stored physical columns.
Related Concepts:
#MySQL 8
#JSON
#Generated Columns
#Functional Index
More MySQL Tuning, Indexing & Concurrency Interview Questions
How does the InnoDB B+Tree Index work internally, and why does Leftmost Prefix Rule dictate composite index column ordering?
Read →
Explain InnoDB Locking Modes: Record Locks, Gap Locks, and Next-Key Locks, and how they prevent Phantom Reads.
Read →
How do you optimize deep pagination (`SELECT * ... LIMIT 5000000, 20`) on large tables with 100M rows?
Read →
How do you interpret MySQL `EXPLAIN ANALYZE` output to diagnose `Using filesort` and `Using temporary`?
Read →
Explain MySQL Table Partitioning (Range, List, Hash) vs. Application-Level Sharding for 1TB+ databases.
Read →