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

0
votes
1answer
16 views

Query Performance Regression Between Different Versions

I have two MySQL databases, A and B. They have identical hardware and schema. They have different data and database A has more data then B. Database A is also running version 5.1, while B is on 5.5. ...
1
vote
2answers
926 views

Find nearest values from MySQL Table

I want to fetch nearest rates for a given value from below table. mysql> select * from rates; +------+----------+----------+ | ID | Type | Rate | +------+---------------------+ | 1 | ...
4
votes
4answers
680 views

optimizing a compare query

Suppose I have two tables, A and B, and I know that size(A) = size(B). I want to confirm that the data in both tables is the same in three given columns, suppose they are X, Y, and Z (there are no ...
5
votes
3answers
4k views

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

There are situations which require having really big query joining several tables together with sub select statements in them to produce the desired results. My question is, should we consider ...
-1
votes
0answers
43 views

Performance issue when we directly insert into #temp table

It might be silly one but I am new to query optimization so I want to know the which query has the better performance select * into #temp from MYTable or Create table #temp ( \\same ...
2
votes
1answer
129 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 ...
1
vote
1answer
81 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 ...
0
votes
0answers
32 views

Performance decline function vs command window

I have a function that I first wrote as a serious of commands. In the SQL command window when executed, those steps take about 8 seconds to return the data set. When those same commands are turned ...
1
vote
3answers
35 views

MySQL select with between optimization

I got issues with some geo IP query. So basically here is create code of table: CREATE TABLE `geo_ip_city` ( `id` INT(5) UNSIGNED NOT NULL AUTO_INCREMENT, `begin` BIGINT(20) NOT NULL DEFAULT ...
0
votes
1answer
78 views

A single, simple update takes 10+ seconds

We are running MySQL 5.1.71-log and have a table with 2.7 million rows. Simple updates to it are regularly taking more than 10 seconds to execute. The table structure is: CREATE TABLE `person` ( ...
0
votes
1answer
52 views

Similar hardware, Same DBconfiguration and Database but different status variables result between Windows and Linux server [on hold]

I have two database servers - one is Windows - 2008 and another is RHEL5 with similar hardware configuration. Both OS are 64 bit. MySQL 5.1.58 community edition is installed on Windows servers while ...
2
votes
2answers
38 views

Joining two Mysql VIEWs takes a very long time

I have a textbox (for which I used jQuery auto-completion) in my PHP form where user can insert an actor/actress name and then by clicking "Search" button, a new window is opened showing list of ...
1
vote
1answer
41 views

Taking the difference in timestamps between successive entries

This is my first foray into DBA.stackexchange, so I'll say up front: this is a query optimization question. If this is not an appropriate question for DBA, let me know and I'll skulk away quietly... ...
0
votes
1answer
83 views

SQL execution 2 seconds with LIMIT parameter, without only 0.2s why?

This is my query and I need to return only 12 rows, but it takes 2 seconds with parameter LIMIT 12 while it takes only <0.2 to process all ~600 results (without LIMIT 12 or with LIMIT 1200 for ...
0
votes
1answer
121 views

How to increase sql server performance? denormalization (making cascade delete off) vs making duplicate data

I have a database with following related table ( sql server enterptise edtion 2014) each state have many citites which each city has may municpalArea and so on State => Cities = > ...
0
votes
1answer
65 views

Skewed Clustered Index

I have a SQL Server database that has a pretty important table (join wise) that contains just over 2.1 million rows. For the VARCHAR column that defines the clustered index, 17.5k rows contain unique ...
1
vote
1answer
42 views

stored procedures location influence their performance?

I have a procedure SP_MYPRO that updates data in MYDB1. 1) the performance of SP_MYPRO would be different if I place it in MYDB2 instead of MYDB1? 2) what if the procedure SP_MYPRO only read data, ...
0
votes
0answers
46 views

Table design for optimum performance

For example let's say I have to create a table called Employee with columns EmployeeID, Name, DOB, Address, DepartmentID[FK]. So I create the table as follows with a primary key and a nonclustered ...
1
vote
2answers
2k views

Oracle query is slower the second time

I have a general question about something I'm seeing in performance tuning in Oracle. I have a query that I'm testing. It's too big and complex for me to provide a simple example, sorry! (I tried ...
3
votes
2answers
101 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 ...
1
vote
1answer
88 views

MySQL: Why queries with subqueries are much faster than single query?

I made few queries on my db lately First query: SELECT `table`.* FROM (`table`) WHERE table.id in(27172172,[...bunch of ids...],27171770) ORDER BY field (`table`.`id`, ...
0
votes
1answer
58 views

Simple query is slow on 4M-rows table

I have in production a MySQL table pageviews with 4M rows that records page views of users on posts. I need to know which posts a specific user have read, but this request takes up to 15 seconds to ...
2
votes
0answers
22 views

PLSQL using cursors or functions to query repeated data

Is it better to declare a commonly used lookup query as a package function or as a function (either returning rows or values or returning a sysref cursor)? Whats the difference? What are the best ...
2
votes
2answers
255 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 ...
0
votes
0answers
30 views

Table maintenance after dump/source

I just completed a major DB migration, all went well. I had a 450 GB DB on old underpowered windows hardware using MySQL 5.0 and I moved everything to much more powerful (512gb memory, server 2012) ...
1
vote
1answer
43 views

optimizing query for slow queries

I have this query in my slowqueries table which essentially is taking about 5-8 seconds to complete the query time: SELECT s0_.id AS id0, s0_.updated AS updated1, s0_.created AS created2, ...
0
votes
0answers
71 views

Performance tuning self-joining MySQL query

I asked a question recently on the correct way to update a table using multiple joins to itself in order to generate the desired values. Updating MySQL innodb table based on multiple joins After ...
11
votes
2answers
4k views

Can a single PostgreSQL query use multiple cores?

In recent versions of PostgreSQL, can we share a query between two or more cores to get a performance boost? Or should we get faster cores?
6
votes
2answers
347 views

Filter table before join

I have a stored procedure that populates the temp table #employee_benefits with a list of Ids. This table ends up being roughly 10,000 rows long. The query below then selects from a table called ...
0
votes
1answer
37 views

Mysql query performance

We have a database table and we found a weird query from old code. We can't really explain it, so we decided to analyze why it could have been done that way. The weird thing is that the results are ...
1
vote
1answer
34 views

Mathematical or Computing Alternatives for MySQL Combination Queries operating toward their Performance Limit

We regularly run a contest that is becoming quite popular. In the contest, we need to select the combinations of contestants from 8 groups whose cost is under a threshold, and who combined have the ...
0
votes
0answers
34 views

Billion records mysql performance with large result sets

I'm currently developing an application which will have several mysql tables where 3 of them have very large amounts of records. The thing is that my app will have to sync each account on a hourly ...
0
votes
0answers
21 views

Intermittent MySQL 5.17 issues - extremely slow queries

I am experiencing major issues with intermittent mySQL issues. To give some background the website runs optimally for the majority of the time, however 3-4 times a day queries that should take a split ...
7
votes
1answer
18k views

How to speed up queries on a large 220 million rows table (9 gig data)?

The issue: We have a social site where members can rate each other for compatibility or matching. This user_match_ratings table contains over 220 million rows (9 gig data or almost 20 gig in ...
5
votes
3answers
452 views

Speed of MySQL query for the most recent 800 entries in chronological order

I have a MySQL database running on an Ubuntu box that pings 250 clients once a minute (24x7). My column names are: ip_address varchar(16), status varchar(7), timestamp datetime Been running it for ...
9
votes
3answers
444 views

Are these two queries logically equivalent?

Are these two queries logically equivalent? DECLARE @DateTime DATETIME = GETDATE() Query 1 SELECT * FROM MyTable WHERE Datediff(DAY, LogInsertTime, @DateTime) > 7 Query 2 SELECT * FROM ...
6
votes
1answer
163 views

Query optimizer recommends adding index instead of using existing index

I am trying to determine why the query optimizer in SQL Server is recommending creating a new index instead of using an existing index that appears to be sufficient for the query. First the table. ...
0
votes
0answers
28 views

database design - upvotes for various types

In the early stages of an application I'm developing, I had two relatively simple tables - one for content and one for upvotes. The content table's schema is as roughly: data | text id | integer ...
1
vote
1answer
83 views

Query performance issues

I need help in improving the performance of the following query. It has the purpose to select one record out of a group of records with an identical ID (doublettes). Rules are that Date2 must be the ...
8
votes
4answers
19k views

How does paging work with ROW_NUMBER in SQL Server?

I have an Employee table that has one million records. I have following SQL for paging data in a web application. It is working fine. However what I see as an issue is - the derived table tblEmployee ...
0
votes
4answers
97 views

Why does this query take 32 sec to execute?

I keep my history records in one table and my pertinent data in another. I want to grab only the files from myfiles that currently have a given status recorded in statuses (ie, the latest status is ...
0
votes
0answers
12 views

first query on my table takes apx: 0 sec but taking too much time on or after 3 to 4 query

I have the following table in mysql database: CREATE TABLE `chronologicallists` ( `sno` int(250) NOT NULL AUTO_INCREMENT, `empSno` int(11) NOT NULL COMMENT 'Employee Serial Number', ...
1
vote
2answers
76 views

Fourth inner join to the same table increases query duration significantly

As soon as I add a fourth inner join to a specific table, the query duration increases significantly: sub 1sec to 45sec. The table in question is deployments d4. The SQL Query: select a.id as ...
1
vote
2answers
619 views

Obvious inefficiencies in this Netezza query?

Why might this query be taking nearly 2 hours to run when similar queries complete within a minute? CREATE TABLE T3 AS SELECT A.*, COALESCE(C.FIELD1,0) AS FIELD1A, ...
1
vote
3answers
57 views

Performance first low, then high after adding index, then keep high after drop index

I am testing performance a complicated select query. It is very slow. using explain, I have found a ALL in type COLUMN. Then I ADD INDEX. result comes out fast. However, when I DROP the INDEX I ...
1
vote
1answer
38 views

How to query a list of all the different errors?

I have a DB which is very big and stores a lot of eventlogs, around 15 million rows. When I try this query, it is totally impossible to get some results. Any idea how I could do this? Maybe indexed ...
0
votes
0answers
59 views

Oracle 11 express vs Oracle 11 enterprise performance

We have a very long select query which is executed via our java application through hibernate. This takes 2 seconds to execute on our local Oracle 11 express db server and takes around 22 seconds on ...
4
votes
1answer
657 views

Oracle: How to gather stats in a logical standby database?

I have a Primary and a (logical) Standby Database. The Schema 'APP' gets synced. Now a User has performance issues running a query against some tables in this 'APP' schema of the Standby Database. On ...
1
vote
0answers
50 views

Summing a column based on uniqueness of another column without using temporary table

I have a table like this: CREATE TABLE `files` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, `hash` char(40) DEFAULT NULL, `size` bigint(20) unsigned DEFAULT '0', ...
1
vote
1answer
36 views

SQL : get rows from two tables relatively

I've two tables named categories and products. Simple structure is given below. categories cat_id cat_name 1 c1 2 c2 3 c3 products prod_id ...