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

how to solve slow running sub query TSQL

I have a problem with a slow running query. I have analysed the query with the sql server profiler but I can't seem to find a good solution. I did find why it is running slow. If ask the full ...
1
vote
1answer
33 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 ...
1
vote
1answer
107 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 ...
0
votes
1answer
29 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
686 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
163 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 ...
0
votes
3answers
68 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
196 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 ...
0
votes
1answer
1k views

Select from a list of values from JSON encoded data

I have saved the details of the product in JSON format. I am wondering how to select from a list of values from a "JSON_ENCODED DATA" For Example I want to select TV with screen_type as LED or LCD. ...
2
votes
2answers
233 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
228 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 ...
0
votes
1answer
59 views

How can I control the order of a query regarding to inner join

I have 3 tables. They have table_a 5m records table_b 20m records table_c 20m records I'm performing the query below. SELECT * FROM table_a a INNER JOIN table_b b ON a.a_id = b.a_id AND ...
1
vote
0answers
54 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 ...
5
votes
2answers
262 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 ...
2
votes
1answer
193 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. ...
0
votes
1answer
51 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
229 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 ...
1
vote
1answer
554 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 ...
0
votes
0answers
78 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
170 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 ...
0
votes
1answer
243 views

Couldn't find the long running SQL under v$session_longops

I have following SQL running from a long time. I couldn't see the status When I tried to look the time remaining for the query to complete. Can someone explain me why I couldn't find the SQL status ...
0
votes
1answer
57 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
171 views

Improve select query performance

The following select query from my application is taking more than 200ms. The table has around 2 million rows. Is there any ways to improve the performance of this query ? select count(id) from ...
2
votes
1answer
508 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 ...
1
vote
1answer
82 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: ...
-5
votes
1answer
337 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 ...
2
votes
1answer
118 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 ...
0
votes
0answers
24 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?
0
votes
1answer
449 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 ...
2
votes
1answer
130 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 ...
2
votes
1answer
38 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
1answer
128 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
81 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 ...
1
vote
1answer
156 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
242 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
131 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
201 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
337 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 ...
0
votes
1answer
84 views

Rerunning query in the same connection takes significantly longer

I am having a weird issue when a running a query that joins some semi-large tables (one table is about 2 million rows and the other about 21 million) together. What happens is the query does not run ...
1
vote
1answer
175 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 ...
0
votes
1answer
72 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
558 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 ...
1
vote
2answers
158 views

Why does “SELECT *” take longer than “SELECT <primary_key>”?

I'm having a problem with a query running on a little PHP program. When I use * to select all the columns, it takes way more to return the result than when I use just one column. The query that is ...
1
vote
0answers
22 views

Difference in the workload related to data-amount and search option

A couple of days ago I was asked why it is necessary to use the first normal form also for separation of names. We have a table which stores private customers (Name+Surname;Information) and business ...
0
votes
0answers
78 views

Latest of group in subquery is bottleneck, how to optimizing query?

See Original question I'm fetching the activity data for the latest activity for each company as part of a bigger query. I've been using the method provided by ypercube, and it works fine, but I find ...
1
vote
1answer
203 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 ...
2
votes
0answers
287 views

mariadb out of memory crash

I have recently migrated from mysql (5.5.36 MySQL Community Server (GPL)) to MariaDB (10.0.10-MariaDB-1~wheezy), along with switching my cloud hosting provider. Both machines have 2GB RAM and 1GB ...
1
vote
1answer
4k views

MySQL SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded

I have database session enable, when website got thousands of visitor. It gives me error [30-Apr-2014 04:27:12 America/Denver] PHP Fatal error: Uncaught exception 'PDOException' with message ...
3
votes
2answers
175 views

How can I eliminate this costly index seek operation from my query?

I have two tables in Sql Server 2008 that look something like this: Transaction_Details: Dataset, Department, Charge_ID, Transaction_Type, dos_month, post_month, amount ...