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

learn more… | top users | synonyms

1
vote
2answers
52 views

DB select query performance when queried by partial key of primary key [closed]

My primary key is composite - {a,b}. Will there be any performance gain if I query only {a} or DB is going to scan the whole table as there is no index only on {a}. How much storage overhead would be, ...
1
vote
1answer
70 views

Is it more efficient to union from sub-query or combine two outer queries?

I searched for this but couldn't find the response I needed. So my apologies if this has already been asked. I'm asking in regards to DB2 but I suppose this would apply, generally speaking, to just ...
1
vote
1answer
63 views

Update records from one table to another in different databases where condition is accomplish

I have two tables stored in two differents DBs and I need to perform some sync between both tables based on lastmodifieddate and lastSyncAt. Let's call them as db1 and db2. This is the DDL schema for ...
1
vote
1answer
94 views

Why is MariaDB 10 refusing to use my index on a date field?

I have the following table definition: CREATE TABLE `bets` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `id_mp` bigint(20) NOT NULL DEFAULT '0', `coin` char(3) COLLATE utf8_unicode_ci NOT NULL, ...
1
vote
1answer
81 views

Postgres not using the index even when rows returned is 5% of the table

I have a postgres table with the following structure: +---------+-------------+-------------+----------+---------+---------+ | id | timestamp | numvalues | text1 | text2 | text3 | ...
1
vote
1answer
96 views

Why SELECT is so slow

I have the following table CREATE TABLE words ( id bigint(20) NOT NULL, `text` int(11) NOT NULL, w1 int(11) NOT NULL, w2 int(11) NOT NULL, w3 int(11) NOT NULL, w4 int(11) NOT NULL, ...
1
vote
2answers
132 views

Awful execution plans after migrating from MySQL 5.5.11 to MariaDB 5.5.41

We just migrated our web site database from a server running MySQL 5.5.11 to one running MariaDB 5.5.41. Everything is working mostly fine, except there's at least one query from our storefront that's ...
1
vote
1answer
93 views

Improve Query Performance

I have the following sqlite3-Tables: db.execute("""CREATE TABLE IF NOT EXISTS STATIONS (StationUID INTEGER PRIMARY KEY AUTOINCREMENT, StationNumber TEXT NOT NULL, ...
1
vote
1answer
196 views

Postgres - Insert performance with indexes

We have a table with about 20 columns in a Postgres 9.2.10 DB. For better performance on some SELECT queries, we plan to add an index on one column with data type timestamp. As the index also can slow ...
1
vote
3answers
145 views

Special function slow in where but not in select

I have kind of a special problem using a levenshtein algorithm in MySql. But I don't think that it's special for levenshtein. This query: SELECT *,levenshtein(word,'Facbook') FROM Words WHERE ...
1
vote
1answer
314 views

set global variable parameter?

My prod_server version is 5.1.47 and want to set the slow query log dynamically for certain period of time. I refered one site(http://toastergremlin.com/?p=276) there is mentioned below 5.1.6 we need ...
1
vote
1answer
149 views

intermittent performance issue - query with multiple CTE and trashed execution plan

I'm trying to optimize a SQL query that shows variable performance (less than 2 to more than 15s). Overview This is a ColdFusion website, with a SQL Server 2008 R2 backend The query is part of the ...
1
vote
1answer
2k views

Optimizing Amazon RDS - MySQLTuner Recommendations / Feedback & Suggestions

I'm currently trying to optimize a database I've inherited for speed. The server is an Amazon RDS db.m3.large instance: 2 VCPUs 7.5 GB Memory 500 GB HDD My current strategy is to do the ...
1
vote
1answer
100 views

Best approach to get last value inserted

I have several devices that report via Edge network their position (and more data) to a UDP server which directly inserts this data in a SQL 2008 r2. I want to know which is the best approach to ...
1
vote
1answer
63 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... ...
1
vote
1answer
48 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 ...
1
vote
1answer
110 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
430 views

Deciding which MySQL execution plan is better

Background: I have a query that I'm trying to speed up, involving 3 tables: omgenvelope has 5 million rows omgcust has 195 rows omginput has 35836 rows I'm obtaining the distinct omginputs ...
1
vote
2answers
160 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
98 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
181 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
221 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
488 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
79 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
133 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
50 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
904 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
142 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
338 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
62 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
689 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
478 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
85 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
596 views

Identical query, tables, but different EXPLAIN and performance

I have two tables levels and users_favorites +--------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | ...
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
2answers
299 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
2answers
10k views

INSERT INTO with SELECT subquery db2 9.5

I have the following query that takes 4 seconds to execute: SELECT TX.* FROM TABLE_X TX INNER JOIN TABLE_Y TY ON TY.VALUE_ID = VALUE_ID_IN AND TY.PROD_ID = TX.PROD_ID WHERE (TX.PLACE_ID1 ...
1
vote
1answer
779 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
1answer
5k views

Get top most parent by nth child id?

Now there is a question we commonly use this technique to maintain the parent child relation i.e we store all the entities in one tables with a parent_id column and all top most parents have 0 in the ...
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
1answer
768 views

MySQL stored routine performance while using PREPARE

Instead of maintaining stored routines for each database in my current environment I have decided to create separate database just for stored routines storage. Mainly I am using them for reporting. ...
1
vote
1answer
38 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
266 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
391 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 ...