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
2answers
1k views

How does compound index help speed up query?

Say we have a bunch of column in mysql table. Latitude Longitude Say we make an index (Latitude and Longitude) Well, the index will sort by Latitude first and IF the Latitude is the same it'll sort ...
3
votes
2answers
607 views

UPDATE query with JOIN taking forever

I'm not very good with databases so please keep in mind even if the solution sounds like something everyone would have already tried, please post it because I likely have not tried it and it would ...
3
votes
3answers
146 views

How can I improve this query?

About a year back I introduced a query which returns a sort of "Customers Also Purchased" data-set. At the time it ran reasonably fast however, as of late it's become very slow, sometimes taking up to ...
3
votes
2answers
507 views

How do I count rows with two properties in one index scan in SQL?

I have a table Units where I have rows ItemId, ItemCreationDate, ItemUnitsCount. ItemId is a primary key and I have a clustered index over it. I need to output the following: for each day I need to ...
3
votes
2answers
283 views

Slow fulltext search due to wildly innacurate row estimates

Fulltext queries against this database (storing RT tickets) seem to be taking a very long time to execute. The attachments table (containing the fulltext data) is about 15GB. The database schema is ...
3
votes
2answers
55 views

Why is MySQL ignoring my indexes?

I have a query select distinct id from patient where company_id in (1) and (name_last like 'peter%' or name_first like 'peter%' or remote_id like 'peter%') running on a table that looks like this ...
3
votes
4answers
573 views

Is database partitioning for performance reasons meaningful in a virtualized environment?

When running SQL server on physical hardware, placing database data and log files on separate disks or RAID arrays can benefit performance, resilience, and maintenance. Partitioning the database over ...
3
votes
2answers
544 views

Efficiently query MAX over multiple ranges

When performing a MIN() or MAX() over a single range covered by an appropriately sorted index, SQL Server does a TOP() and so returns the value after fetching just one row. When the search criteria ...
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
604 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
1answer
988 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
645 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
244 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
1k 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
117 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
132 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
3k 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
523 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
119 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
458 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
9k 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
343 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
210 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
348 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
230 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
55 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
72 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
98 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
2answers
68 views

Slow queries on production Server

I am a developer who has been asked to look at a database slowness issue. I have had a look at the waits on the server and retrieved the following using a query from Red Gate's wait stats white paper. ...
3
votes
1answer
263 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
1answer
507 views

Scaling PostgreSQL TRIGGER(s)

How Postgres triggers mechanism scales ? We have a large PostgreSQL installation and we are trying to implement an event based system using log tables and TRIGGER(s). Basically we would like to ...
3
votes
3answers
306 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
150 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
427 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
220 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
458 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
313 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
3answers
287 views

Slow SQL Server authentication, but fast SAN, fast querying?

I am asking you after days of research but I think I don't have required skills to understand what is happening. I have a slow authentication on a SQL Server 2008R2, which leads to a timeout for the ...
3
votes
1answer
406 views

Extremely long query time after upgrading to Postgres 9.3 from 9.1

We're attempting to upgrade from Postgres 9.1 to Postgres 9.3 on a FreeBSD 9.2 zfs box. Performance is noticeably slower across the board, but we have one query that has gone from 353ms to >200000ms. ...
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
194 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
507 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
82 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
812 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 ...