Questions tagged [query-optimization]

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 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 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 tag on your question along with the tag for your database: , . , 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 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

  1. Join ordering
  2. Query planning for nested SQL queries
  3. Cost estimation
  4. Parametric Query Optimization
  5. Multi-Objective Query Optimization
  6. 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/

7749 questions
15
votes
2 answers

Handling optional/empty data in MongoDB

I remember reading somewhere that the mongo engine was more confortable when the entire structure of a document was already in place in case of an update, so here is the question. When dealing with "empty" data, for example when inserting an empty…
Oxymore
  • 185
  • 1
  • 1
  • 8
15
votes
3 answers

How to optimize a table for fast inserts only?

I have a log table that will receive inserts from several web apps. I wont be doing any searching/sorting/querying of this data. I will be pulling the data out to another database to run reports. The initial table is strictly for RECEIVING the…
Neil N
  • 24,862
  • 16
  • 85
  • 145
15
votes
5 answers

Unable to optimize MySQL query which uses a ORDER BY clause

I'm using Drupal 6 with MySQL version 5.0.95 and at an impasse where one of my queries which displays content based on most recent article date slows down and because of the frequency of being used kills the site performance altogether. The query in…
optimusprime619
  • 754
  • 2
  • 17
  • 38
15
votes
5 answers

MYSQL query - getting totals by month

http://sqlfiddle.com/#!2/6a6b1 The scheme is given above.. all I want to do is get the results as the total of sales/month... the user will enter a start date and end date and I can generate (in PHP) all the month and years for those dates. For…
Apurva
  • 461
  • 1
  • 5
  • 19
14
votes
3 answers

Where might I find a method to convert an arbitrary boolean expression into conjunctive or disjunctive normal form?

I've written a little app that parses expressions into abstract syntax trees. Right now, I use a bunch of heuristics against the expression in order to decide how to best evaluate the query. Unfortunately, there are examples which make the query…
Billy ONeal
  • 104,103
  • 58
  • 317
  • 552
14
votes
3 answers

How to optimize a slow "select distinct" query across three tables, 40k rows, that only returns 22 results

So I have this query written by someone else that I'm trying to refactor, which pulls some features/materials for an item(shoes, generally). There are a lot of products, and thus a whole lot of joining table-entries, but only a few few features that…
Kzqai
  • 22,588
  • 25
  • 105
  • 137
14
votes
8 answers

Query against 250k rows taking 53 seconds

The box this query is running on is a dedicated server running in a datacenter. AMD Opteron 1354 Quad-Core 2.20GHz 2GB of RAM Windows Server 2008 x64 (Yes I know I only have 2GB of RAM, I'm upgrading to 8GB when the project goes live). So I went…
Chad Moran
  • 12,834
  • 2
  • 50
  • 72
14
votes
2 answers

Why mysql explain analyze is not working?

Besides having mariadb 10.1.36-MariaDB I get following error. EXPLAIN ANALYZE select 1 MySQL said: Documentation 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to…
14
votes
1 answer

bitwise mask vs IN() efficiency in sqlite?

I have two ways to select a set of entries from the database: SELECT ... WHERE `level` IN (1,2,4,8) LIMIT ...; or SELECT ... WHERE `level` & mask LIMIT ...; There are 4 'levels' total, numbered 1,2,4,8 (for reasons of ability to use the same…
SF.
  • 13,549
  • 14
  • 71
  • 107
14
votes
2 answers

MySQL view performance TEMPTABLE or MERGE?

I have a view which queries from 2 tables that don't change often (they are updated once or twice a day) and have a maximum of 2000 and 1000 rows). Which algorithm should perform better, MERGE or TEMPTABLE? Wondering, will MySQL cache the query…
Paulo Amaral
  • 747
  • 1
  • 5
  • 24
14
votes
2 answers

Efficiently Computing Significant Terms in SQL

I was introduced to ElasticSearch significant terms aggregation a while ago and was positively surprised how good and relevant this metric turns out to be. For those not familiar with it, it's quite a simple concept - for a given query (foreground…
Alix Axel
  • 151,645
  • 95
  • 393
  • 500
14
votes
1 answer

How to describe performance issue in relational database?

I have a query running in a relational database that doesn't fulfill the expectation of the users. What information should I provide and what should I avoid, so that I can receive an effective help on this site?
Marmite Bomber
  • 19,886
  • 4
  • 26
  • 53
14
votes
3 answers

How to optimize an ORDER BY for a computed column on a MASSIVE MySQL table

I have a very large (80+ million row) de-normalized MySQL table. A simplified schema looks like: +-----------+-------------+--------------+--------------+ | ID | PARAM1 | PARAM2 | PARAM3 …
cbrumelle
  • 171
  • 1
  • 7
14
votes
3 answers

Lots of DESCRIBE queries in Zend Framework

I just set up FirePHP in Zend and I'm noticing a huge number of DESCRIBE queries. Some pages have 50 or more identical queries all on the same table. e.g. 0.00198 connect NULL 0.00449 DESCRIBE `nodes` NULL 0.00041 SELECT `nodes`.* FROM…
Tamlyn
  • 22,122
  • 12
  • 111
  • 127
14
votes
14 answers

Handling large databases

I have been working in a web project(asp.net) for around six months. The final product is about to go live. The project uses SQL Server as the database. We have done performance testing with some large volumes of data, results show that performance…