For questions about making database queries run faster. Please include table and index definitions in your question. Please use additional tags to specify your query language and server maker. Some of these questions may belong on https://DBA.stackexchange.com, especially if they involve reindexing, query plans, etc.
See also sql-tuning.
SQL is a declarative language that can have many ways to find the same data. As a SQL programmer, you declare what you want from it, and it figures out how to get your result. Because of how databases are structured, and how they retrieve data, the same results can be retrieved in various ways that the query interpreter needs to choose between.
Using sargable predicates, using indexes, and optimizing how the query works can make it run more quickly. Restructuring the query, removing unnecessary portions of the query, or using different features of different Database Engines can all be helpful.
If the question is not (just) about how to write the query, but involves how to create new indexes, or how to optimize the server, you may get good answers on DBA.stackexchange.com.
The query optimization attempts to determine the most efficient way to execute a given query by considering the possible query plans.
For Query optimization you may consider following implementation areas:
- Join ordering
- Query planning for nested SQL queries
- Cost estimation
- Parametric Query Optimization
- Multi-Objective Query Optimization
- Multi-Objective Parametric Query Optimization
If you're asking "Why is my SQL query so slow?" or something like that, you need to do a little extra homework to get good answers from the Stack Overflow community.
What database?
Put this query-optimization tag on your question along with the tag for your database: sql-server, mysql. postgresql, oracle or whatever database you use. Don't use more than one database tag, please. Optimization works somewhat differently from database to database.
How many rows and how fast?
Please figure out approximately how many rows you expect in your result set, and approximately how many rows are in each table involved with the query. Mention those numbers in your question. Please mention how long you hope your query needs to take, and how long it's taking. (That is, tell us what you mean by slow).
Don't oversimplify
Don't try to simplify your question by replacing your actual SELECT column lists with *. Using * in SELECT clauses can be harmful to query performance compared to specifying the list of columns you need.
Show your table definitions, with indexes
In your question include your table definitions and index definitions for each table and view involved in your query. Make sure your question shows all indexes in all relevant tables. When you're asking a performance question, please don't oversimplify your question by omitting indexes or columns you think might be irrelevant. The strangest things can be relevant to performance!
Show your database's explanation of how it satisfied your query
In your question please show the EXPLAIN PLAN output (it's just EXPLAIN in some table server makes and models) for your query.
Things to know
Creating an appropriate index for a slow query is often, not always, a good solution to a problem. Answers on query-optimization will often contain a suggestion for an index.
Avoid creating indexes on lots of single columns. Single-column indexes are often not very useful to speed up complex queries.
As your database grows you will, most likely, discover new slow queries. Operations can be trivial on hundreds of rows, but much more time-consuming on tens of thousands of rows. In a growing database, plan to review performance issues routinely.
If you use Microsoft's SQL Server Management Studio, this tip is for you: Right-click in a query window, then select Show Actual Execution Plan, then run the query. The execution plan display sometimes recommends a new index to create.
Reference
Please be aware that query performance optimization can be an inexact science. This is a good reference. http://use-the-index-luke.com/
