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

1
vote
1answer
101 views

Minimizing Page Fetches

I have a complicated database structure and am trying to use it to retrieve Records based on multiple selection criteria from several tables. As a general rule, is it better to attempt to use ...
1
vote
1answer
35 views

Mysql super slow

I have a database of movies that I downloaded, it is around 51mb in size. One of the tables has more than 750k records(all separate insert statements(I believe this is bad)). The rest aren't so bad ...
0
votes
0answers
38 views

Why is PostgreSQL doing a seq scan on column != column?

Why is PostgreSQL doing a table scan on column != column? Isn't this query guaranteed to return no results? Isn't anything != anything guaranteed to be always false for any record on a not null ...
0
votes
0answers
30 views

Slow query (Wordpress)

Can I speed up somehow the following query? Somehow to stop from creating a tmp table/filesort. Tried a couple of indexes but no luck. mysql> explain SELECT wp_posts.ID FROM wp_posts LEFT JOIN ...
1
vote
0answers
80 views

How to improve query performance in PostgreSQL? [closed]

I have the following query to be execute within a second but getting time for execute 2.8 minutes: Query: Using Common Table Expression. explain analyze with temp_cte as ( Select ...
2
votes
1answer
69 views

High I/O for part of query

I have a large query that I'm trying to tune. I write lots of queries but haven't done a lot of tuning. I've included a screenshot from SQL Sentry Plan Explorer Free (SSPEF): In the above part of ...
1
vote
2answers
246 views

Improve query performance when selecting almost all rows with many “group by” columns

I have a table with 20 columns and about 600,000 records. The maximum row size is only around 100 bytes. The table is repopulated every few days, but the number of records remain about the same. For ...
0
votes
0answers
16 views

View in View slower to execute?

Sometimes it's easier and clearer arranged to create a View from another View. Can this cause some speed losses in relation to selecting everything just in one Big and long View-Command?
1
vote
0answers
14 views

Optimize finding the next batch of data from A to be crunched and inserted into B

I'm using PostgreSQL to store data produced by a large distributed computation. There are several stages to this computation; the idiom we've settled on is that there's a table containing the results ...
0
votes
1answer
106 views

Improve MySQL Query Performance - Too many records

I am using MySQL RDMS for a contact management system. Now a days my database grew in size and contains more than 1 million records. Checking duplicate phone numbers became a big issue during ...
3
votes
1answer
511 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
1answer
37 views

Difference between DATE(time_field) AND time_field in a WHERE clause

I have 3 tables: users movements unique_ids I want to get the records gotten from march until now of the users registered in march, counting the unique id of their movements with a distinct and the ...
1
vote
1answer
160 views

How can I use mongoDB efficiently in this scenario?

I'm trying to build a system of documents that have hundreds of tags assigned to them, but each document-tag association has a certain condition it has to also meet so the document would be returned: ...
1
vote
1answer
30 views

What is the generally accepted method and tools for profiling MySQL queries?

Profiling database queries is difficult, and dependent on database technology. In MySQL, what is the usual (or, in deference to that, a great) process for profiling a query? For example: Run the ...
0
votes
1answer
26 views

selecting rows that have n foreign key conditions satisfied

I'm trying to filter a master table rows based on 2-more conditions from the relative foreign key relation, i came up with this which is working fine, but i just can't convince my self that it's that ...
5
votes
2answers
137 views

An efficient way to compare two large data sets in SQL

Currently, I'm comparing two data sets, that contain unique StoreKey/ProductKey combinations. The 1st data set has the unique StoreKey/ProductKey combinations for sales between begin January 2012 and ...
1
vote
1answer
141 views

Understanding execution plans using TVPs

I have some slow running queries (8 seconds across 1M records grouped by months) and have been trying to make sense of the execution plans. We use around 10 TVPs to send in a set of filters that the ...
6
votes
4answers
15k 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 ...
5
votes
2answers
245 views

Cannot tune database any further; what next?

We have a vendor-supplied application. It is under support and we are talking to their developers (we have their largest database size by an order of magnitude), but in the meantime, we have the ...
0
votes
3answers
60 views

How to return last used record?

I have simple table called ips with 2 field ip and address, which schema is following CREATE TABLE IF NOT EXISTS `ips` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `address` varchar(255) ...
0
votes
2answers
42 views

Increased query_cache_size, Queries slowed drastically on increased traffic

I increased our servers query_cache_size to 2gb, hoping for performance benefits. After sometime the query performance would drastically decrease. Once I dug in deeper, I found the queries in State ...
4
votes
1answer
114 views

Search, filter and group across all columns in a tall and wide dataset quickly [closed]

We have certain datasets which may span 80 to 300 fields, across 300,000 to 35 million rows. For each dataset, we want to produce a web based analysis tool (a web grid) which would allow our users ...
2
votes
2answers
60 views

Super simple mysql query taking too long

I have the following: SELECT * FROM autorepair WHERE zip_code IN (91316,91356,91416,91426,91436) AND specialty = 'transmission' ORDER BY ABS(91316 - `zip_code`) LIMIT 0,10 Indexes: -zip_code ...
1
vote
1answer
82 views

SQL hourly data aggregation in postgresql

I am a newbie with database so I'm seeking your help with this one. I have a table containing time series data. 2012/01/01 00:10, 10 2012/01/01 00:30, 5 2012/01/01 01:00, 10 2012/01/01 01:40, 10 ...
1
vote
0answers
35 views

Why is one of these queries orders of magnitude slower than the other?

Sorry for the vague headline question. Essentially, and I know this is generally terrible practice, but I have query with nested subqueries as follows: SELECT * FROM ibt1 WHERE basetype = 60 ...
1
vote
1answer
80 views

Have Postgresql query planner use nested loop w/ indices over hash join

I'm having a problem with some StackOverflow-schema related data loaded into PostgreSQL 9.3.4. I have a query that is taking about 10x longer than it should, due to the fact that it is choosing to use ...
1
vote
1answer
52 views

Slow queries InnoDB occasionaly

I have the next table: CREATE TABLE `sessions` ( `session_id` char(32) COLLATE utf8_unicode_ci NOT NULL, `session_expires` int(11) NOT NULL DEFAULT '0', `session_data` varchar(10000) COLLATE ...
0
votes
1answer
39 views

How to optimise very simple query with subquery that only needs to execute once

SELECT products_id, products_model, products_iban_no, products_allocation_number FROM products WHERE products_model IN (SELECT products_model FROM products GROUP BY products_model HAVING ...
0
votes
1answer
75 views

Best way to store country and city information

I always wondered what is more efficient between two. There is one "main" table where I store all user related informations. Also, there are two more tables, country and city tables. city table ...
0
votes
0answers
45 views

INDEX SKIP SCAN performance on Index where first columns only contain 1 value

We're running an Oracle 11.2.0.3 database and have a table with above 10 columns in it. There is a non-unique index over all these columns. For simplicity in this example, lets assume the columns are ...
4
votes
1answer
346 views

How does order of index affect performance in SQL Server?

I am wondering how does order of index affect performance (ASC, DESC) ? If it affects, why ? And which order should I choose ?
9
votes
2answers
1k 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?
1
vote
1answer
67 views

Performance issues when extracting scatter plot data

First off: Sorry for the title, couldn't think of anything better. Feel free to change it though. Ok, assume I have the following table: ...
1
vote
1answer
141 views

MySQL with slow queries

I have a website running on Wordpress Multisite + Woocommerce with over 5700 users on a Nginx + Apache server. I recently experienced some down time but didn’t get any errors, just a blank page. I ...
0
votes
1answer
52 views

How can I write queries as fast as possible in MySQL?

I'm looking for two things: Awesome manual about writing fast MySQL queries. Tool which will decompose my query into discrete steps, and give estimations of each step. I've seen the next tool, ...
2
votes
1answer
62 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 ...
-5
votes
1answer
87 views

Recommendation for mysql please :MySQLTuner [closed]

>> MySQLTuner 1.3.0 - Major Hayden <major@mhtx.net> >> Bug reports, feature requests, and downloads at http://mysqltuner.com/ >> Run with '--help' for additional options ...
0
votes
0answers
17 views

Analize Query in MySQL Server 5.1

I try use MySQL Workbench 6 to analyze the cost, but they don't show the cost of query. How i get this costs?
2
votes
1answer
95 views

Query returning unused stored procedures

When I run SP_askbrent@asof = '2014-05-20 16:00' a period where the database was timing out, I'm getting dozens of CREATE PROCEDURE xxx entries. When I run the following query, the results show the ...
1
vote
1answer
31 views

Will composite indexes or unique indexes help with query performance?

I need help with identifying indexes to increase performance of the following query. I'm currently using v5.5 with InnoDB. I cannot modify the query. However, I can add indexes as needed. The ...
2
votes
0answers
81 views

poor and/or unreliable InnoDB performance

I recognize that there are a ton of posts like this, but after reading up on InnoDB performance problems, nothing I tried has helped. I have a database containing four InnoDB tables. The largest ...
1
vote
1answer
61 views

LOAD DATA stuck at Null State MySQL

I'm stuck trying to move one table to another, due to columns now no longer having proper references/meaning to their column titles. In doing this, I've created a new table with the same number of ...
0
votes
0answers
72 views

group by and order by making query very slow in mysql?

Query works fast: select SQL_NO_CACHE users.id, users.name from useres left join user_statistics on users.id = user_statistics.user_id where users.is_deleted = 0 group by users.id ...
0
votes
1answer
43 views

Query with INNER JOIN takes long time than expected?

I have the following query, which takes longer than expected. Anybody help me find a faster alternative? SET SESSION MAX_JOIN_SIZE=18446744073709551615; SELECT claims.claim_id, ...
2
votes
1answer
126 views

Does SQL check each And condition if first condition fails? [duplicate]

For a query of below kind does sql process data for each And condition if first And condition fails? If Emp_ID is not 12 for a row then does it check condition of Emp_Name? So how SQL process this ...
3
votes
1answer
218 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 ...
1
vote
1answer
83 views

Why does innodb_flush_method = O_DSYNC provide such a performance boost with SAN-backed storage?

I've seen some people suggest that O_DSYNC should be used with a SAN. The MySQL docs have this to say about O_DSYNC in general: In some versions of GNU/Linux and Unix, flushing files to disk with ...
1
vote
1answer
171 views

Retrieve next queue item

I have a simple table in SQL Server 2012 which implements a processing queue. As data has been inserted the query to retrieve the next item has gone from <100ms to a constant 5-6secs. I would be ...
0
votes
1answer
42 views

NTEXT,Indexing, and lob logical reads

We have an query that would benefit from a covering index but one of the columns that would be returned is a NTEXT data type. I know this can't be added to the index and I also know it would be good ...
4
votes
2answers
520 views

Why is my plan guide not being used?

We recently ran into the tipping point issue and some of our report queries that used to complete execution within a couple of seconds are now taking more than 2 min because the query optimizer simply ...