A class of issues where a database executes a query inefficiently due to the optimiser selecting an innapropriate query plan.

learn more… | top users | synonyms

9
votes
3answers
751 views

Can spatial index help a “range - order by - limit” query

Asking this question, specifically for Postgres, as it has good supoort for R-tree/spatial indexes. We have the following table with a tree structure (Nested Set model) of words and their ...
15
votes
3answers
592 views

difference in execution plans on UAT and PROD server

I want to understand why there would be such a huge difference in execution of the same query on UAT (runs in 3 sec) vs PROD (run in 23 secs). Both UAT and PROD are having exactly data and indexes. ...
9
votes
1answer
2k views

SQL query for combinations without repetition

I need a query which can be used in (or as) a function and retrieves all combinations of n values. And I need all combinations of length k where k = 1..n. Extended sample input and result so input ...
5
votes
2answers
817 views

Why does MySQL choose this execution plan?

I have two queries, select some_other_column from `table` order by primary_index_column asc limit 4000000, 10; and select some_other_column from `table` order by secondary_index_column asc ...
2
votes
1answer
111 views

Combining columns in index

With reference to RolandoMySQLDBA's answer on MySQL: Index when joining to tables not being used (Performance optimizing question) He mentioned Make sure you build an index that involves the ...
6
votes
1answer
589 views

Algorithm for finding the longest prefix

I have two tables. First one is a table with prefixes code name price 343 ek1 10 3435 nt 4 3432 ek2 2 Second is call records with phone numbers number time 834353212 10 ...
6
votes
2answers
7k views

Learning to Optimize SQL Queries and Understand Execution Plans - Resources?

I find myself writing more and more SQL queries at work (mostly Oracle 11g, but some SQL Server 2005-2008) and have started creating some pretty complex views for the rest of the analyst team. They ...
9
votes
2answers
1k views

LIKE uses index, CHARINDEX does not?

This question is related to my old question. The below query was taking 10 to 15 seconds to execute: SELECT [customer].[Customer name],[customer].[Sl_No],[customer].[Id] FROM [company].dbo.[customer] ...
6
votes
1answer
229 views

How to Optimise Query

I have a database structure similar to this, CREATE TABLE [dbo].[Dispatch]( [DispatchId] [int] NOT NULL, [ContractId] [int] NOT NULL, [DispatchDescription] [nvarchar](50) NOT NULL, ...
4
votes
2answers
823 views

Does a TRIGGER improve the performance?

I understand that TRIGGERs are useful, as they take care of subsequent queries, but do they have an advantage from performance point of view too? Replacing two or three queries as INSERT INTO table1 ...
2
votes
2answers
1k views

How do I force a JOIN to use a specific index in MySQL?

I have a query that JOINs 2 tables; lineitem and part, select sum(l_extendedprice* (1 - l_discount)) as revenue from lineitem force index for join (l_pk), part where ( ...
10
votes
4answers
2k views

Is it possible to increase query performance on a narrow table with millions of rows?

I have a query that is currently taking an average of 2500ms to complete. My table is very narrow, but there are 44 million rows. What options do I have to improve performance, or is this as good as ...
6
votes
1answer
147 views

How should we handle rows which won't be queried once they are old in PostgreSQL?

We have a table in a PostgreSQL database which is growing on the order of millions of rows per day. Each row consists of: ID Foreign user ID Date and time Other data The date and time aren't ...
4
votes
1answer
188 views

What is the name of this type of query, and what is an efficient example?

The purpose is to find a parent, given it's children. For example, say you have a marketing package (aka a "combo"), and want to match it based on the products in it. Example table/data: create table ...
4
votes
2answers
371 views

Why is a secondary index chosen over a clustered index for SELECT COUNT(*) …?

In this query: select count(*) from largetable; a secondary index is chosen: mysql> explain select count(*) from largetable; ...
3
votes
5answers
355 views

Query optimization [Oracle]

The Query: select JOIN_RESULT.batch_id as BATCH_ID, JOIN_RESULT.unique_doc_id as DOCUMENT_ID, JOIN_RESULT.brand as BRAND, JOIN_RESULT.message_date_time as UPLOAD_DATE, ...
2
votes
1answer
214 views

Slow query performance

My database contains 20,000 rows and the following query is slow. Please help me to speed this up: SELECT r.report_id, r.user_id, u.user_name, u.user_mail, ...
14
votes
1answer
853 views

Stored procedures vs. inline SQL

I know stored procedures are more efficient through the execution path (than the inline sql in applications). However, when pressed, I'm not super knowlegeable about why. I'd like to know the ...
4
votes
3answers
651 views

Some book suggestions for performance tuning

I am in the process of improving performance on our database application. While I am not a DBA, I am pretty comfortable with SQL. I am after a book that can help me understand how queries written in ...
12
votes
2answers
2k views

How (and why) does TOP impact an execution plan?

For a moderately complex query I am trying to optimize, I noticed that removing the TOP n clause changes the execution plan. I would have guessed that when a query includes TOP n the database engine ...
10
votes
3answers
280 views

Indexes for SQL query with WHERE condition and GROUP BY

I am trying to determine which indexes to use for an SQL query with a WHERE condition and a GROUP BY which is currently running very slow. My query: SELECT group_id FROM counter WHERE ts between ...
4
votes
5answers
1k views

How can I prove that the following T-SQL is bad for performance?

I have recently inherited a codebase with a large amount of Stored Procedures. The system they are supporting is encountering numerous performance problems which I am looking in to. A number of the ...
4
votes
4answers
9k views

High CPU usage on SQL server - Slow queries [closed]

Our MS SQL Server is using about 95% of the CPU-power. After a server (hardware) restart, or a SQL-Service restart, the usage is 0% and slowly increases over the course of 1-3 days. Depending on how ...
2
votes
3answers
2k views

Is it normal to use many Triggers?

I have many so-called summary columns throughout my database to count number of rows in other tables. For example, counting the number of comments by a user (a column in the user table). Currently, I ...
3
votes
3answers
2k views

Is it better to seperate a big query into multiple smaller queries?

There are situations which require to have really big query joining several tables together with sub select statements in them to produce the desired results. My question is, should we consider using ...
3
votes
1answer
582 views

MySQL Handler_read_rnd_next == tablescans without indexes?

I am in the process of tuning our Percona 5.5 server, and have been using percona's tools to plot our database's performance. In the MySQL Handlers Plot, i have been noticing alot of ...
2
votes
0answers
381 views

MySQL: Improve performance for one row insert into table with unique constraint

I have a table with ~ 40 mil records, with a unique index on the url collumn, of type varchar(255). Now the insert speed is about 30/s, is this expected? how could I improve it? I can't use bulk ...
1
vote
2answers
159 views

Optimize simple query using ORDER BY date and text

I have a query to a table in Postgres with an order based on a date field and a number field, this table has 1000000 records The data types of the table are: fcv_id = serial fcv_fecha_comprobante = ...
0
votes
2answers
560 views

MySQL: can not get rid of “Using filesort” in a simple query

Simple query but can not get rid of "using filesort": CREATE TABLE IF NOT EXISTS `online` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `uid` int(11) NOT NULL, `expiration` int(11) NOT NULL, ...
3
votes
3answers
1k views

MySQL optimization - year column grouping - using temporary table, filesort

I have a transactions table which is having 600,000 records, I need to list the count for the dashboard on financial year basis. The table used is MyISAM. I tried adding index for the transaction date ...
2
votes
1answer
98 views

T-SQL Query runs quickly on local PC, but slow on SQL Server?

I have a query with a few joins and a couple of basic SUM(...) statements, and no parameters. It's been running without issue for months - but has suddenly started running very slowly on the server ...
2
votes
2answers
103 views

Convert to Self JOIN

I have a correlated sub query, which is taking 16 Minutes to execute. How can I convert it to join or optimize it. SELECT SSF.SM_StockCode, SSF.ST_ItemSize FROM Stock AS SSF WHERE ...
2
votes
3answers
191 views

How to get all non deleted messages from one user

I have two tables one for messages called default_messages and the other for recipients called default_recipient. This is how SQL for those table looks like: CREATE TABLE IF NOT EXISTS ...
1
vote
1answer
562 views

Query taking non-consistent time to execute [closed]

I have a query which takes large time to execute sometimes.When i checked the speed it was taking 15 seconds.But actually it should run faster than that.When i again checked the query it again ...
1
vote
1answer
106 views

Performance impact of including result column in indexed columns

I have a database (in MySQL, using InnoDB) that I am using for texture identification in a 3D image. In it, there will be a table of texels(texture pixels) with hue included, in the form of: texelid ...
1
vote
1answer
115 views

Several values from different tables

I'm totally lost here and not know how to get this to work and hope any can help me here. I have several tables: default_users, default_profiles, default_status, default_comment and latest ...
1
vote
0answers
143 views

Get all comment.message, status.message, users.* and profiles.* where users.user_id exists in friend.friend_id or friend.user_id

I need some help building this query because it's a bit complicated at least for me. I need to get all comment.message, status.message, users.* and profiles.* where users.user_id exists in ...
0
votes
2answers
165 views

How to get rid of using temporary when using DISTINCT in MySQL?

When I do the following, I get Using where; Using index; Using temporary. How do I get rid of Using temporary? http://sqlfiddle.com/#!8/f61a2/3/0 CREATE TABLE `test` ( `a` varchar(45) NOT NULL, ...
0
votes
1answer
316 views

optimize big sql query

My query has to return a statistics for example for march containing productname and price and its sidedishes (1:n relation) with the right price of each sidedish and the right tax for each sidedish ...