For questions about making database queries run faster. PLEASE INCLUDE QUERY PLANS, AND 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.
Please include table and index definitions for all relevant tables in your question. Please also include the query plan: the EXPLAIN output. Read on for more explanation.
If you use postgresql this is a good guide to pulling together the right information for your question.
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 https://DBA.stackexchange.com.
Your database's query planner, also known as its query optimizer module attempts to determine the most efficient way to execute a given query by considering possible query plans.
You'll hear a lot about query planners in this query-optimization tag. Each DBMS software product has its own query planner. Here's a description of one query planner.
How to ask a good query-optimization question
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. Please include this information.
What database and version?
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. And, please mention the version of your database server.
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!
Very important: the names and data types of columns included in each index, with their order.
Show your database's explanation of how it satisfied your query
In your question please show the EXPLAIN PLAN output (it's just EXPLAIN or SHOWPLAN 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 SSMS -- 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.
Things you might do when optimizing queries
- Join ordering
- Query planning for nested SQL queries
- Cost estimation
- Parametric Query Optimization
- Multi-Objective Query Optimization
- Multi-Objective Parametric Query Optimization
Reference
Please be aware that query performance optimization is an inexact science. This is a good reference. http://use-the-index-luke.com/
