🚀 How To: Suggest Indexes

Providing a SQL query and getting index suggestions in AI Query, means analyzing the SQL query that was provided and suggesting indexes that can be added to your database schema to improve the performance of that query.

When you execute a SQL query against a large table with many rows, the database engine needs to scan through all the rows to find the data that matches the SQL query criteria. If the table has an appropriate index on the columns used in the SQL query's WHERE clause or JOIN condition, the database engine can use the index to quickly locate the rows that match the criteria, reducing the time and resources needed to execute the SQL query. Let's take a look at how to generate index suggestions for a SQL query.

Steps

On the AI Query Dashboard, navigate to Query Playground and select the Database.

💡

For example purposes we will use the Demo database.

Select Suggest Indexes as the Query Type for the Query Prompt.

Below is the SQL Query we want to use to provide us with Index Suggestions:

SQL Query

SELECT Item, COUNT(Item) AS Count 
FROM Orders 
INNER JOIN Customers 
ON Orders.CustomerNumber = Customers.CustomerNumber 
WHERE Customers.Country = 'USA' 
GROUP BY Item 
ORDER BY Count DESC 
LIMIT 1;

Now paste the above SQL Query and click ▶ to generate Index Suggestions.

SQL

CREATE INDEX idx_orders_customer_number ON Orders (customer_number);
CREATE INDEX idx_customers_country ON Customers (country);

You'll see that you get Index Suggestions based on the SQL Query you specified! 😉

Optimize a SQL Query Core Concepts