Do Covered Indexes Really Boost MySQL Performance? A Real-World Test

Test Method

Delete existing indexes, then create a single‑column index on user_id and a composite index on user_id plus the image name. Measure the index size and the time required to insert the same number of records.

Space Cost

Single‑column index: 14.5 MB

Composite index: 143 MB

Write Performance Cost

With roughly one million existing rows, insert 10 rows and 1,000 rows, each test repeated three times.

Single‑column index – 10 rows: 0.063 s, 0.049 s, 0.051 s

Composite index – 10 rows: 0.062 s, 0.068 s, 0.065 s

Single‑column index – 1,000 rows: 5.172 s, 5.148 s, 5.031 s

Composite index – 1,000 rows: 5.939 s, 5.691 s, 5.264 s

Conclusion

For about one million records, the composite index consumes roughly ten times more space (≈143 MB vs 14.5 MB), which is acceptable. Insertion performance is only slightly slower, with negligible impact for read‑heavy workloads. The experiment also underscores the value of data‑driven reasoning over intuition when evaluating database optimizations.