Use this tag for questions about improving the performance and/or efficiency of database queries.

learn more… | top users | synonyms

1
vote
2answers
156 views

Complicated data

I have a requirement i got from my father. He gave me a table and wanted me to digitalis it. So that he can the system can do all the dirty jobs for him ( that's why there are developers in the world, ...
1
vote
1answer
90 views

PostgreSQL: Another seq scan where an index scan is expected

Here's the query: SELECT DISTINCT ON(points.track_id) participants.contest_number, points.track_id, points.lat, points.lon, points.alt, points.timestamp, points.h_speed, ...
1
vote
1answer
145 views

Can having a LIMIT on DELETE degrade performance dramatically?

I have a database project in php and mysql. For, hopefully redundant, data security I added a LIMIT 1 to the delete($uniqueID) php function we use. Nobody cared because records really aren't ...
1
vote
1answer
202 views

Reducing execution time for this gnarly SQL

I'm creating an order system of some kind. There is a function that I use to get orders (in PHP) and retrieve the required information about the order. However, it seems that I am pulling a lot of ...
1
vote
1answer
426 views

PostgreSQL 9.3 - Performance Issue: Counting table entries slower with jdbc postgres driver

I'm using PostgreSQL 9.3 and want to ask if it is possible to improve the performance of my queries based on the following database schema: CREATE TABLE "entities" ( "id" BIGSERIAL, "type" INT ...
1
vote
1answer
76 views

Will Rebuild and Reorganize improve Database Migration Script performance execution?

I would like to know if a Database Schema Migration Script (generated by Visual Studio 2008 or greater) will run faster if my target database got indexes rebuilt and/or reorganized. Note: For all ...
1
vote
1answer
127 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` ( ...
1
vote
1answer
97 views

Help optimizing very slow mySQL query

I have a query which takes about 6 secs to execute. SELECT * FROM (SELECT ord.ID, op.name AS prodName, op.code, ord.date, ord.email, op.total, stat.ID AS statusID, stat.value AS status, ...
1
vote
1answer
36 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
669 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
140 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
10k 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 ...
1
vote
2answers
1k views

postgresql being slow on count distinct for dates

I have a very simple, but very big, table. Its schema is like this (yadda int, yadda1 int, yaddate date, ... other stuff). Now, yaddate has an index by itself and it is also in other indexes ...
1
vote
1answer
290 views

Firebird query performance with text search, also some numbers

I have situation as follows: I'm having a table OFERTY of 10k+ records, and when I select * from it, I get PLAN (OFERTY NATURAL) Executing... Done. 656 fetches, 0 marks, 0 reads, 0 writes. 0 inserts, ...
1
vote
1answer
61 views

How can I optimize this query?

I'm trying to select the time field from five tables and return the oldest time. The userid field in every table is indexed. Here's the query: ( SELECT `time` FROM `eventlog` WHERE userid = ...
1
vote
1answer
621 views

Different thread states in query execution

My MySQL 5.1 based Intranet system is facing very bad performance during high load. When observed through MonYog, I found below thread states: We can simply understand the different thread state by ...
1
vote
1answer
81 views

Poor query performance in MySQL

I have a 10 million record table in a MySQL 5.5 InnoDB. It's running in a 16GB RAM server with good CPU and fast HD. When I run the following query SELECT DISTINCT knowledge_id, ...
1
vote
1answer
441 views

Slow running query with inner SELECT in where clause

I have a query that looks like the following: USE DATABASE3; GO SELECT t1.COL1 AS COLUMN1, t2.COL2 AS COLUMN2, t1.COL3, t1.COL4 AS COLUMN4, t1.COL5 AS COLUMN5, ...
1
vote
1answer
83 views

Query tuning - performance

Could you please help me tune this query. It takes over half an hour to run! I want to identify repeat bookings (can only be identified using an annoying varchar field - email) UPDATE #Repeats --a ...
1
vote
1answer
2k views

Using OFFSET in a large table by a simple sub-query

The slow performance for using OFFSET in large table has been widely discussed in various blogs, and the most efficient way is to use an INNER JOIN as SELECT * FROM table INNER JOIN ( SELECT id FROM ...
1
vote
1answer
1k views

SQL Server performance for tables having large number of columns [closed]

I have a requirement where I need to create a table with about 600 columns. Out of 600 columns about 200 columns will be XML columns. Other column will be of other datatypes such as int, varchar, ...
1
vote
1answer
6k views

Different execution plan for the same query if I change a value in the predicate

I am getting two different execution plans for the same query only by changing the value in the predicate, the first one uses nested loops and the other one hash join which takes a lot longer. ...
1
vote
2answers
291 views

MySQL (InnoDB) UPDATE SET performance with value in WHERE AND clause

If an UPDATE query is performed where the values being SET are also part of the WHERE clause, in the form of an additional AND statement on the column being set's value, is there a significant ...
1
vote
1answer
700 views

“copy to tmp table” for 24 hours, then timeout; when ALTERing to add PK & AI

I downloaded the Wiktionary XML dump, and ran mwdumper.jar to insert the data into MySQL. Following the advice in the manual, I commented out the PRIMARY KEY AUTO_INCREMENT part of the CREATE TABLE ...
1
vote
3answers
3k 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 ...
1
vote
1answer
62 views

What ways are there to limit the processor / ram requirements of a specific query?

Every once in a while, I need to run some very expensive query on a production server. Think spaghetti joins. This is likely to affect the server operation, and I don't want that. What ways are there ...
1
vote
2answers
1k 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
2answers
1k views

How to use and optimize subquery on 100 million rows

What I'm trying to do is running a job on more than 100 million domains which haven't processed before. I have two tables, "domain" and "domain_setting", on every batch (10.000 domains per batch) I'm ...
1
vote
1answer
35 views

Simple query not using indexes

My query is not using indexes. It is a simple query. Can someone explain why that's the case? The query: select TIME_FORMAT(start_time,'%H:%i') start_time, TIME_FORMAT(end_time,'%H:%i') ...
1
vote
1answer
250 views

Avoiding performance hit from GROUP BY during FULLTEXT search?

Is there any clever way to avoid the performance hit from using group by during fulltext search? SELECT p.topic_id, min(p.post_id) FROM forum_posts AS p WHERE MATCH (p.post_text) AGAINST ('baby ...
1
vote
1answer
371 views

Simplify and optimize a complex query

Our site has a chronological event feed that uses MySQL. We have noticed that some of the queries tend to run for quite a bit longer than others. For example, some queries for our heaviest users take ...
1
vote
1answer
402 views

Query tuning help needed (Hash Match and Table Scans)

I need help optimizing the following query (returning ~8k rows): SELECT A.sys_id, 'AppSvrRels' = CAST(SUBSTRING((SELECT (', ' + T.name) FROM ( ...
1
vote
1answer
2k views

Slow complex query with group/order

I have a problem with a very important query in my application. It is very slow when adding group and order to it. So the full query is: SELECT m.id, m.title, m.title_text, ...
1
vote
2answers
657 views

Simple queries taking ages to run after changing from MyISAM to InnoDB

I have a quite large (300GB) table on MyISAM, which I've "converted" to InnoDB, and performance for a specific query has dropped through the floor. When I say "converted", I can't afford to take the ...
1
vote
1answer
183 views

Get and count all blog posts and all users that fits a LIKE condition (two sepparated queries)

I've this tables: -- -- Table structure for table `default_cmvblog` -- CREATE TABLE IF NOT EXISTS `default_cmvblog` ( `id` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(100) COLLATE ...
1
vote
1answer
4k views

PostgreSQL 9.1 - Query on VIEWS taking a lot of time

Using PostgreSQL 9.1, we are having problems while executing queries on a VIEW of PostgreSQL. Following is the situation: We have a partitioned Table "buz_scdr" over which we have built a VIEW ...
1
vote
1answer
160 views

Reviewing all new MySql queries on staging environment

The problem I am trying to solve: It's well known that not all developers know what is EXPLAIN in MySql, and not every knows how to use it. Large part of them consider MySql as a black box, no matter ...
1
vote
1answer
44 views

Get wall_post and profile data for several users

I'm trying to build a query for fetch wall_posts and profile fields for several user_id. First see the images at: http://www.dropmocks.com/mBjQ7P. The table with id_user_rq, id_user_ap is the table ...
1
vote
1answer
219 views

MySql - How can I improve this query?

I have the following table: http://pastebin.com/XH6FvhZ8 and the following query: SELECT * FROM (SELECT * FROM `job_logs` ORDER BY id DESC) AS job_logs_tmp WHERE (job_logs_tmp.user_id = ...
1
vote
1answer
567 views

Oracle Benchmarking (SQL_NO_CACHE …)

I want to benchmark some queries on an oracle System from a JDBC application. However, the database seems to cache at some point in time. However, this is not intended in this case. Is there ...
1
vote
2answers
132 views

Improving MySQL efficiency

I have a table now containing over 43 million records. To execute SELECT, I usually select records with the same field, say A. Will it be more efficient to divide the table into several tables by ...
1
vote
2answers
48 views

Optimising a query to get rows where x is between pairs of values from another table

PostgreSQL 9.4.5 In my database there are 2 tables, vcfentries and exons. I want to query for rows in vcfentries where pos is between a list of pairs of positions, exonstart and exonend in the ...
1
vote
1answer
47 views

What are the cons of implementing faceted search with WHEREs and JOINs?

I'm implementing a filter search system (I understood today that is called faceted search) for an ecommerce website. The fron end looks something like this: Country: [Select a country] Categories: ...
1
vote
0answers
68 views

Performance measuring on complex query

Now that I have overworked a very very complex sql statement by going over all those little parts and investigating on their missing indexes, index design, queries column order, join vs. exists... I ...
1
vote
0answers
41 views

Slow query - PostgreSQL 9.2

I've got a Query that takes some time to be executed... Can you guys help to improve the total time? Thank you! QUERY: SELECT file.inode_id AS file_id, file.parent_inode_id AS ...
1
vote
0answers
42 views

Oracle, Cursor_Sharing = Force and Queries with literal comparisons forcing FTS

I have the following scenario: Oracle 11.2.0.3 A 3rd party application, that requires cursor_sharing = TRUE A table (T2310) with a indexed field (C1000000161) that has no duplicated values Result ...
1
vote
0answers
70 views

High load on server because of one query

I have one query running from last stable version of wordpress on causing high load on my centos 6.7 server I enabled slow query log thought /etc/my.cnf file to record which sql cause this problem ...
1
vote
0answers
33 views

Query slow on subscriber but fast on publisher (Merge Replication)

As the title suggests, I am having issues with performance with my subscriber. I run the exact same query (it's a recursive query with a few joins but nothing too serious). I can run this query on my ...
1
vote
0answers
89 views

How Can I Increase Performance of This MERGE Statement?

I have a INSERT INTO/MERGE statement that is part of a stored procedure that needs to be really efficient. The execution plan shows that 90% of the procedure is due to the following statement shown ...
1
vote
0answers
42 views

Window function vs group by method: find value for MAX ID

I'm trying to understand the performance issue with Window function when i select value for MAX ID --Old group By method SELECT tj.MainProcedure , tj.StartTime , tj.EndTime , ...