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

3
votes
3answers
2k 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 ...
3
votes
1answer
1k views

Query taking long on table with low record count

I have an Oracle flat table that grew to just below 800MB. The table was truncated and new records are being inserted. Currently there are <40 records in the table but a select * takes >30secs. ...
3
votes
5answers
594 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, ...
3
votes
4answers
2k views

Optimize Query with Derived Table

I have the following query select ps.id, ps.title , IFNULL(s.likes,0) as num_likes , IFNULL(s.comments,0) as num_comments , IFNULL(s.ratings,0) as num_ratings , IFNULL(s.views,0) as num_views , ...
3
votes
1answer
3k views

Optimizing large database query (25+ million rows, using max() and GROUP BY)

I'm using Postgres 9.3.5 and I have a large table in the database, currently it has over 25 million rows and it tends to get even larger rapidly. I'm trying to select specific rows (all unit_ids with ...
3
votes
3answers
4k views

GROUP BY one column, while sorting by another in PostgreSQL

How can I GROUP BY one column, while sorting only by another. I'm trying to do the following: SELECT dbId,retreivalTime FROM FileItems WHERE sourceSite='something' GROUP BY seriesName ...
3
votes
1answer
919 views

How to correctly return a query result only if it isn't NULL?

I'm writing a PL/pgSQL function that creates a cursor for a query that I need to check whether it returns something. What I'm doing is this: Run the query Check whether it returns something. If it ...
3
votes
2answers
594 views

Should I add multiple secondary indexes to search

I have a search screen on my app where the users can use up to 4 parameters to search. I have written a stored procedure to facilitate the search. Select ID, FirstName, LastName, ...
3
votes
1answer
243 views

Why are my queries getting really slow?

I own a WordPress installation, and Updated to 3.4.1. After the upgrade, I experienced a very strange slow-down. After all, I used the define('SAVEQUERIES', true); in my wp-config.php with ...
3
votes
3answers
2k views

Speed up search across multiple columns

We have a table: user_info (user_id,domain_id,first_name,middle_name,last_name,work_place,.,.,.) Now, we have a user search that search across three columns first_name,last_name and work_place. ...
3
votes
1answer
995 views

Can you have a WHERE clause with a greater than operator on a composite key?

I'm a web developer (not DB admin!) having performance problems processing a 1.7m record table on MySQL. Here is that table : CREATE TABLE `bbcmapimp_pc_raw` ( `postcode` char(8) NOT NULL, ...
3
votes
1answer
93 views

DB2 CASE statement faster with parentheses around entire WHEN

Trying to optimize slow queries, someone on my team come across this. Having this in our stored proc: CASE WHEN MYCOL BETWEEN @STARTNUM AND @ENDNUM AND RECTYPE = 'RO' THEN 1 ELSE 0 ...
3
votes
1answer
122 views

Help with indexing narrow table with many millions of rows

I have a good amount of experience with SQL Server, but not much with indexes and I fear I'm a little bit out of my depth in trying to figure out how to correctly structure a table that has 100+ ...
3
votes
2answers
2k views

Dramatic decrease in SQL Azure performance after scaling to new edition

Yesterday, we scaled one of our databases in Azure from the old Business edition to the new Standard one (S2). Since then, it has been behaving rather erratically. We have a relatively simple table ...
3
votes
1answer
499 views

MySQL queries are 10 to 100 times slower after OS reboot

I have a MySQL server running on Ubuntu. If I restart the MySQL, everything is fine. If I however restart the OS, my queries take anywhere from 10 times to 100 times as long. The only "solution" to ...
3
votes
1answer
114 views

Every query plan statistic says my query should be faster, but it is not

I have a bit of a odd situation. I have two queries that produce the same result, according to SSMS and SQL Sentry Plan Explorer the first query should cost 95% and the 2nd query should cost 5%. ...
3
votes
1answer
443 views

SQL Server WHERE clause on CLR method (spatial) peformance

I have a query that joins to a table containing 2 million rows and need to filter on its geometry column by its geometry type (using geom.STGeometryType()). The query takes less than a second to run ...
3
votes
2answers
8k views

SQL SERVER - Linked Server and query performance

I have a performance problem related to SQL Server/linked servers/views. I hope you can help me understand what is the best way to do what I want =). I have a database K with 3 linked servers ...
3
votes
1answer
342 views

Query Performance Issue

I have this below query which is behaving bit weird . well Weird in the sense that I couldn't find complete explanation for this. Version : Sql Server 2008 R2 Enterprise No fragmentation . ...
3
votes
2answers
205 views

Query taking too much time

My query is taking too much time to execute. Please help me here. mysql> explain select pcm.catalog_id from cat_produ_catal_map_defer pcm, cat_catal_catal_map_defer ccm, cat_catal_defer c ...
3
votes
2answers
336 views

Do MySQL sub-queries essentially use as much overhead as separate queries?

If I have a query like this: SELECT name FROM ( SELECT * FROM `table` WHERE id IN (1,40,300) ORDER BY AnotherColumn ) From a bandwidth and overhead standpoint, is that the same thing as doing ...
3
votes
1answer
229 views

Get profiles values where message.sender_user_id = profile.user_id

I have this tables: default_messages CREATE TABLE IF NOT EXISTS `default_messages` ( `id` int(11) NOT NULL AUTO_INCREMENT, `subject` varchar(150) COLLATE utf8_unicode_ci NOT NULL, `message` text ...
3
votes
2answers
2k views

Copy huge amount from 1 table to other

I have two tables with same structure I need to copy over all tables from table A to table B, the problem is that some records from table A already exist in table B so that made the Import Fail. So I ...
3
votes
1answer
45 views

Why is the secondary selective index not used when the where clause filters on `value()`?

Setup: create table dbo.T ( ID int identity primary key, XMLDoc xml not null ); insert into dbo.T(XMLDoc) select ( select N.Number for xml path(''), type ) from ( ...
3
votes
3answers
71 views

Is it better to create another table to possible null values in relational databases?

I'm designing a relational database to use in MySQL. I have the next situation: In one table there are fields that in the most of situations are NULL. The fields would be like: Table name: tabla ...
3
votes
1answer
61 views

Pagination performance, with subquery, inner join and where

I am trying to optimize following query (this is most simplified version I could came up with): SELECT tr.Id, StatusDate FROM ( SELECT tr.Id, tr.StatusDate FROM mon.ArchivedTaskResults_201504 ...
3
votes
1answer
196 views

Unused index in range of dates query

I have a query that is not using existing indices and I do not understand why. The table: mustang=# \d+ bss.amplifier_saturation Table ...
3
votes
3answers
278 views

Nested table as condition prevents using index

The following query joining a subquery does a TABLE ACCESS FULL when the master-table uses nested table as condition. When the condition is a simple =, the same query does an INDEX RANGE SCAN as ...
3
votes
1answer
143 views

How do I deal with query getting slower when a select parameter is not specified inline?

I faced this problem twice with SQL Azure and two different queries so far. Something like this: select top(100) * from SomeView WHERE AccountId='actual-guid-typed-in-here' runs super fast but ...
3
votes
1answer
402 views

Most efficient queries on flags in TSQL: bit field or table join?

Looking for the most efficient database structure for selecting rows from a large table based on one or more true/false values. For example, whether or not a user has opted in to marketing ...
3
votes
1answer
215 views

Optimizing complex join predicates [closed]

Table Definitions CREATE TABLE [dbo].[Pin_Mtg] ( [MtgId] bigint NULL, [CntyCd] char(5) NOT NULL, [BatchDt] int NOT NULL, [BatchSeq] int NOT NULL, [MtgSeq] tinyint NOT NULL, ...
3
votes
3answers
432 views

Why does the query cost change so much and how to prevent it

The query causing me the issue is too big, but the piece that seems to be the core is quite simple, I will try to go with that: The query has an structure like: SELECT ... a lot of stuff ... ...
3
votes
2answers
309 views

Sudden Query Performance Degradation On A Linked Server

I have an application that uses a view on a SQL Server 2008 R2 instance. This view actually queries a linked server (SQL Server 2008) that has replicated data from our main ERP database. Yesterday, we ...
3
votes
1answer
1k views

MYSQL Slow SELECT with multiple joins and ORDER BY

I have this query SELECT r0_.id AS id0, r0_.deleted_at AS deleted_at1, r0_.updated_at AS updated_at2, r0_.created_at AS created_at3, r0_.is_vehicle_offer AS is_vehicle_offer4, ...
3
votes
1answer
2k views

Is there any difference on performance when choosing DISTINCT or GROUP BY to bring distinct values?

I would like to know if there is any difference concerning performance when choosing DISTINCT or GROUP BY to bring distinct rows from a query. I've tried comparing the execution plans, but they seem ...
3
votes
2answers
186 views

Best Indexing Strategy for Query with Equality[A], Range[B], Group By[C], AND Order By[count(P)]

I have a poorly performing query: SELECT user_id, count(item_id) as count FROM table items WHERE category = 'magazine' AND created_at > 1384754400 GROUP BY user_id ORDER BY count(item_id) desc ...
3
votes
1answer
494 views

Need help with long running query

UPDATE: Adding a clustered index to each work table covering all fields reduced the query run times to su-bsecond. I consider this question closed. Thanks for taking the time to read this post - ...
3
votes
2answers
81 views

Looking for a possible multi-column index

I have a table highscore containing the columns: game (text) date (timestamp) score (integer) more irrelevant ones... The query most often run on it is: SELECT * FROM highscore WHERE game = ...
3
votes
1answer
798 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 ...
3
votes
1answer
1k views

MySQL 5.5.8 InnoDB Foreign Key, JOIN performance

We are using aws/rds, MySQL 5.5.8. all InnoDB. For performance purposes, we have merged some data-elements into varchar fields; mini-sized int and date type fields; added covering indexes; ...
3
votes
1answer
136 views

How to search for an ID and its descendants in this query?

I have the following tables: Project Unit ------------- --------------- ID | IDUnit ID | IdParent I have to select all projects that have the IDUnit ...
3
votes
2answers
547 views

Query planner thinks doing a sequential scan on a very large table is faster than using an index?

I've got a query that selects the most favorited photo for each of the last 12 editions for a particular domain (this is just a segment of a larger query, but the problem seems to stem from here): ...
3
votes
1answer
173 views

MySQL table architecture

Background information: I have a table containing upload information, every day I get more and more uploads but most of the queries I run center around information gathered within the last 12 months; ...
3
votes
3answers
1k views

PostgreSQL 8.3: Slow GROUP BY on large table

I have a table with about 10 million records. I want to do a simple group by, but it's using a sequential scan and is slow... select run_id, count(*) from result group by run_id; I have an index ...
3
votes
1answer
1k views

Mysql - show results from 3 tables excluding NULL or EMPTY values for certain fields

I have 3 simple tables with polls, users and votes. I'd like to show all polls ordered by clean count of votes (sometimes either poll_id or user_id don't get inserted). Furthermore I'd like to show ...
3
votes
2answers
2k views

MySQL COUNT Query taking too long time to execute

I have below query that takes 7 secs to run SELECT COUNT(*) AS `count` FROM `yuldi`.`businesses` AS `Business` LEFT JOIN `yuldi`.`businesses_categories` AS `BusinessesCategory` ON ...
2
votes
2answers
234 views

DISTINCT not using an index [duplicate]

I have table (2139868 rows): COLUMN1 NUMBER(20,0) COLUMN2 XMLTYPE COLUMN3 XMLTYPE COLUMN4 VARCHAR2(50 BYTE) COLUMN5 VARCHAR2(50 BYTE) COLUMN6 VARCHAR2(50 BYTE) COLUMN7 VARCHAR2(50 BYTE) COLUMN8 ...
2
votes
4answers
2k views

Remove sub-string from WHERE clause

I have a stored procedure that is using the SUBSTRING function in the WHERE clause. SELECT DISTINCT ColumnBB, ColumnCC FROM TableAA WITH (NOLOCK) WHERE SUBSTRING(ColumnAA, 1, 17) = @VariableA; ...
2
votes
1answer
418 views

Performance of query with complex where clause on 10M rows

We have a query that destroys our production server when run. It is part of a reporting functionality and the bad part looks as follows: SELECT DISTINCT mt.ID AS ID FROM ...
2
votes
2answers
310 views

What can be done to further enhance performance of Multiple Join and Aggregate Queries?

I have a typical star schema simulated here, and I am mentioning two queries: first query simply joins the fact table with 2 dimension tables and 1 calendar table, and the second query joins and ...