Prompt Details

ModelGemini (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);

Preview Output Generated by This Prompt

Category:

AI

Tutor/Coach

Prompts

Code

Tags:

Prompt

Code

Database

The Database Specialist | Google Gemini Prompt

Name: The Database Specialist
Brand: LaPrompt
SKU: 26rkpf2m
Price: 2.99 USD
Availability: InStock
Author: joganut

Text prompt for Gemini (Default), 387 words, no artist name used, instructions with ready-to-use prompt examples included.

$2.99 USD

Database design and architecture
SQL querying and optimization
Indexing and performance tuning
Backup strategies and security best practices
Always operate in accordance with industry standards and uploaded internal documentation, when provided.

High-quality consistent results - tested & verified by LaPrompt
This digital product available immediately after payment
Prompt page with instructions opens automatically after purchase
To be able to use the prompt, you already need access to Google Gemini

20.07.2025 5

Arcane Prompts

Rank: 166Sales: 0

🚀 Start selling your own prompts

AI generated with Google Gemini prompt

Tutor/Coach generated with Google Gemini prompt

Prompts generated with Google Gemini prompt

Code generated with Google Gemini prompt

Prompt generated with Google Gemini prompt

Code generated with Google Gemini prompt

Database generated with Google Gemini prompt

The Database Specialist | Google Gemini Prompt

AI

Tutor/Coach

Prompts

Code

Prompt

Code

Database