Model

Gemini (Default)

Example input

User's Goal]: "My goal is to optimize a slow query that finds the 10 most recently active users for our dashboard."

[Database System]: "MySQL 8.0"

[Environment]: "Production"

[Schema Context]: "The main table is user_activity with columns activity_id (PK), user_id (FK), and activity_timestamp (DATETIME). The user_id column has an index."

[Existing Code/Error]: "The current query is SELECT user_id, MAX(activity_timestamp) AS last_active FROM user_activity GROUP BY user_id ORDER BY last_active DESC LIMIT 10;. It's taking over 30 seconds to run on a table with 50 million rows."

[Constraints]: "I have read-only access to the production database. I need to know if there's a better way to write the query or what indexes I should recommend to the lead DBA."

Example output

Understood. This is a classic and very common performance problem when dealing with large time-series or activity tables. Let's break down the issue and the most effective solution.

1. Problem Analysis: Why the Original Query is Slow

The reason your query is slow, despite the index on user_id, is that the database cannot use that index to efficiently perform the operation. The query plan looks like this:

    Full Table Scan: MySQL must read all 50 million rows from the user_activity table.

    Group and Aggregate: It then groups all of those rows by user_id and finds the MAX(activity_timestamp) for each group.

    Sort: Finally, it sorts this large, aggregated result set in descending order to find the top 10.

The index on user_id only helps if you were searching for a specific user (e.g., WHERE user_id = 123). It does not help the database find the latest timestamps efficiently.

2. The Optimal Solution: A Composite Index

The most effective solution is to create a new composite index. This index will pre-sort the data in the exact order the query needs, allowing MySQL to find the answer almost instantly without scanning the entire table.

This is the command you should recommend to your lead DBA.
SQL

-- RECOMMENDED INDEX FOR PERFORMANCE
-- This composite index covers both the sorting and grouping operations required by the query.
-- By ordering by timestamp first, the database can instantly find the latest entries.

ALTER TABLE user_activity
ADD INDEX `idx_useractivity_timestamp_userid` (activity_timestamp DESC, user_id);