How a Covering Index Cut MySQL Query Time from 40ms to 30ms

Scenario

In a product there is a picture table with nearly 1 million rows. A frequently executed query retrieves a user's pictures. The table has fields user_id , picname , and smallimg . An index on user_id (uid) already exists.

Before Optimization

Running the query with

SELECT SQL_NO_CACHE picname, smallimg FROM pics WHERE user_id = 17853;

ten times averages about 40 ms. Using EXPLAIN shows the user_id index is used as a const lookup, indicating good performance.

After Optimization

Because the statement is simple, the main improvement is adding a composite index ( user_id, picname, smallimg ) named uid_pic . Re‑executing the query ten times reduces the average time to about 30 ms. EXPLAIN now shows the composite index is used and the Extra column contains “Using Index”.

Conclusion

The “Using Index” message means a covering index is in effect. A covering index contains all columns needed by the query, allowing MySQL to satisfy the request from the index alone without a table lookup, reducing I/O and improving efficiency. In this case the composite index covers both the filter column ( user_id ) and the selected columns ( picname , smallimg ), which explains the performance gain.