In the context of a database, optimisation refers to the process of the query optimiser selecting an efficient physical execution plan.

learn more… | top users | synonyms

1
vote
1answer
132 views

MySQL: Optimize query with a range and distinct count

I have a query on a large table with millions of rows that looks like this: SELECT COUNT( DISTINCT clicks.tx_id ) AS unique_clicks_count , clicks.offer_id FROM clicks WHERE ...
0
votes
0answers
22 views

Subquery optimization

I was reading through this article - http://explainextended.com/2009/09/18/not-in-vs-not-exists-vs-left-join-is-null-mysql/ I am working with an existing design. What I want is to be able to select ...
2
votes
2answers
105 views

How to handle too many inserts?

I have a database that is accessed from remote locations in the world with very long delays and poor transfer speeds. I currently transfer the data using a transaction since either all or no data ...
3
votes
0answers
41 views

Is it possible to get seek based parallel plan for distinct/group by?

An example from this question shows that SQL Server will choose a full index scan to solve a query like this: select distinct [typeName] from [types] Where [typeName] has a non-clustered, ...
10
votes
3answers
13k views

Optimizing queries on a range of timestamps (two columns)

I use postgresql-9.1 with ubuntu 12.04. I need to select records inside a range of time: my table time_limits has two timestamp fields and one property integer. Indeed there are other info columns ...
0
votes
0answers
30 views

Oracle query optimization time

Is there any way to get how long it takes the optimizer to optimize a query in Oracle?
1
vote
1answer
24 views

Finding the end of a Relationship chain optimally

I have a table that contains an id column, a parent column, and it's child column. So for every record, I know what record comes before it and what will come after it in a chain relationship, but from ...
6
votes
3answers
1k views

How recording of every change of a row in a database is generaly stored?

In a project I'm working on, every change to the rows in some tables of the database must be tracked for further audit or rollback. It must be easy to find who modified the row, from which IP address ...
1
vote
3answers
122 views

Text Storage and Database Design Optimization in a SQL Server Database

Given a database with a number of columns (c), where some of these columns contain text that is one of multiple values due to a set CONSTRAINT on the columns, what is the most efficient way to ...
0
votes
2answers
4k views

How to force higher values of open_files_limit or table_open_cache?

I'm having a frustrating problem where setting my.cnf to values higher than I currently have them does not 'take'. This is MySQL 5.6.14 on MacOS X 10.8.3 I'm trying to address dropped connections ...
1
vote
1answer
717 views

Slow insert with MySQL full-text index

I use a full-text index in a MySQL table, and each insert into this table takes about 3 seconds. It seems that MySQL rebuilds (a part) of the full text index after each insert/update. Is this right? ...
2
votes
1answer
95 views

Why is the CTE much worse than inline subqueries

Im trying to better understand how the query planner work in postgresql. I have this query: select id from users where id <> 2 and gender = (select gender from users where id = 2) ...
0
votes
1answer
236 views

mysql fulltext query is very slow

I have one table for User detail in MySql with about 500000 records in it. I have also created fulltext index on firstname, lastname field on this table. but when I am trying to search any single ...
1
vote
1answer
49 views

No value to update but still an TABLE UPDATE Operator

I see this type of code a lot in my databases IF EXISTS(SELECT 1 FROM sample WHERE ID=@id) BEGIN UPDATE sample SET name='cat' WHERE id=@id; END So from my primary analysis it does 2 scan in ...
2
votes
2answers
156 views

Queryplan changes depending on filter values

I created a clustered index on a table expecting it to make the queries with ranges perform better, but, different values in the where clause can produce differente query plans, one uses the clustered ...
3
votes
1answer
115 views

How to determine cost/benefit of adding an index?

According to Craig Ringer: While it's usually a good idea to create an index on (or including) your referencing-side foreign key columns, it isn't required. Each index you add slows DML operations ...
1
vote
1answer
475 views

MySQL hangs in killed state

I have a strange situation where a mysql OPTIMIZE query will never end. After killinh it, it remains in the "killed" state. Scenario to reproduce: OS: CentOS 6.5 MySQL: 5.6 Engine: MyISAM (with at ...
0
votes
2answers
178 views

How to optimize indexes on MySQL query with various sorts

I have an INNODB table levels: +--------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | ...
0
votes
0answers
22 views

HDD being used 100% even when data files are not on that disk

I am facing very high disk IO. Ihave 2 HDD - 1 is a SATA disk and the other is a SSD. The sata disk is showing 100% disk utilization. My.cnf is as follows [mysqld] datadir = /mnt/disk1/mysql ...
1
vote
1answer
107 views

Optimize large table design

First, I am a complete novice when it comes to databases, but have been given the task to speed up queries on a large set of data. (Hundreds of millions of records) The current implementation is a ...
0
votes
1answer
538 views

INSERT take too much time

I have in my MySQL DB a few tables Most of the time, I'm only doing INSERT in my db. I have up to 4 connections to the DB, each one inserting into a different table. There cannot be two connections ...
0
votes
2answers
2k views

Optimize IN clause in where query with order by - MySQL

I am trying to optimize a query that using IN clause in WHERE to avoid file sorting. To make it easy , I created the following sample which shows the problem. Here is my query: SELECT * FROM `test` ...
0
votes
2answers
1k views

mySQL Optimization Suggestions

I'm trying to optimize our mySQL configuration for our large Magento website. The reason I believe that mySQL needs to be configured further is because New Relic has shown that our SELECT queries are ...
4
votes
4answers
2k views

How can I speed up “show columns” on MySQL?

My application depends on running "show columns" for certain tables. It takes about 60ms to run, whereas all our other queries take under a ms. Querying information_schema directly is even slower. ...
0
votes
1answer
28 views

Index for a Postgres query with a sort and an equality

This query is very slow: EXPLAIN (ANALYZE, buffers) SELECT * FROM "Follows" AS "Follow" INNER JOIN "Users" AS "followee" ON "Follow"."followeeId" = "followee"."id" WHERE "Follow"."followerId" ...
0
votes
0answers
40 views

sql taking < 1 sec to run in sql developer, 37 on the application side

As the title says, we have a big discrepency between a sql query we're running from our java application (jdbc thin client) and the same query run in sql developer. I clear the cache of the query ...
0
votes
2answers
70 views

Optimizing a Postgres query with a large IN

This query gets a list of posts created by people you follow. You can follow an unlimited number of people, but most people follow < 1000 others. With this style of query, the obvious optimization ...
0
votes
1answer
625 views

Are there any disadvantages to partitioning on financial year?

Our current set up has one table per financial year (May 1- April 30). Each table has approx 1.5 million rows. We have about 8 years of data, and will obviously be adding each year. The majority of ...
-1
votes
0answers
20 views

Optimize a query sorted by a join value [duplicate]

This query fetches the likes on a post from people you follow, sorted by the number of followers they have: EXPLAIN (ANALYZE, buffers) SELECT * FROM "Likes" AS "Like" INNER JOIN "Users" AS "user" ...
0
votes
1answer
126 views

getting “table is full” error even if the table size is much smaller than tablespace

Currently I am using MySQL NDBCluster 7.3.5. I have created one UNDOFILE hhme.dat with INITIAL_SIZE=200m. Also one tablespace hhmefep_tbs with DATAFILE hhmefep_tbs.dat having INITIAL_SIZE=500M ...
1
vote
1answer
411 views

Optimising a query of an SSRS dataset

Consider the query given below: SELECT * FROM ((SELECT c.cust_id , c.username , REPLACE(qs.seg_type_ref_key_02, 'N/A', 'Non Vip') casino_group, ...
8
votes
5answers
751 views

Does SQL Sentry Plan Explorer work?

Does SQL Sentry Plan Explorer work as advertised and is it legitimate? Are there any gotchas or something to be concerned about? It looks like it shows the hot path in color commpared to SSMS's ...
0
votes
1answer
24 views

MySQL script writing slow

I'm using php and the exec() function to run 2 MySQL script files one after another The first file has approx 2k update statements (see below) in and is 114kb in size. UPDATE table1 SET is_active = ...
1
vote
1answer
281 views

How to improve the performance of SELECT queries with multiple JOINs on a 50GB database hosted on AWS m3.medium MySQL RDS?

Example query: SELECT p.ListAgentEmail, p.ListAgentFirstName, p.ListAgentLastName, p.ListingKey FROM Property_RES AS p INNER JOIN Property_RES_COUNTIES_OR_REGIONS ON ...
0
votes
1answer
51 views

Best query to exclude existing friends from query

I have a User table / id_user / username / .. friends table (pair AB and BA): / friend_of / friend_to and a friend_request table (used for pending friend requests) / id_req/ sent_from / sent_to ...
2
votes
1answer
66 views

How to speed up select distinct?

I have a simple select distinct on some time series data: SELECT DISTINCT user_id FROM events WHERE project_id = 6 AND time > '2015-01-11 8:00:00' AND time < '2015-02-10 8:00:00'; And it ...
1
vote
2answers
95 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
2answers
74 views

How can one optimize a query involving a sort and a LIKE?

Here is the query: EXPLAIN ANALYZE SELECT * FROM "Users" AS "User" WHERE "User"."name" LIKE 'garr%' AND "User"."id" NOT IN (2449214) AND "User"."hellbanned" IS NULL AND ...
0
votes
1answer
26 views

What is different between fixed-size disk pages and disk-based?

As per my knowledge, i understand fixed page size as size of page which used for read and write data but i am not able to understand what does mean of disk based ? I read these words from this paper ...
3
votes
0answers
104 views

Slow transactions, many locks

At high load conditions the server app stops responding to client because begin/commit statements are executing very slowly (some take 15 seconds). The queries are simple - insert, update two columns ...
3
votes
2answers
168 views

Moving data from table with VARCHAR(50) fields to table with numeric fields increases table size

I want to understand why migrating data in a table with all VARCHAR(50) fields to a table with optimized smaller types caused the new table (containing 61,065,164 rows) to be 4.46 GB, which is larger ...
1
vote
2answers
69 views

Are nonclustered indexes used when joining tables?

I know that joins of type Sort-Merge don't use nonclustered indexes, because they work by sorting both tables and then joining them. But I've read recommendations to noncluster index all FKs, I ...
1
vote
0answers
47 views

How to optimize inefficient query in PostGIS

I'm doing some queries on a PostGIS, and some of them take a really long time ( > 60 secs). My queries are like this, except querying different tables (like osm_placesbelow). This query takes about ...
1
vote
1answer
19 views

How to optimize search restricted by boolean column?

I want to optimize a query1 of the form SELECT yid, xid FROM x WHERE is_principal; Here, is_principal is a non-null boolean column of x. If I run EXPLAIN QUERY PLAN on this query, the output I get ...
25
votes
1answer
3k views

USING construct in JOIN clause can introduce optimization barriers in certain cases?

It was brought to my attention that the USING construct (instead of ON) in the FROM clause of SELECT queries might introduce optimization barriers in certain cases. I mean this key word: SELECT * ...
1
vote
1answer
50 views

Incorrect unit allocation size. error comes when Tuning the Database

Here I am Using the SQL IDERA tool for checking the Index and performance of the Database, then i found below error, can any one tell what is it talking about. Incorrect unit allocation size. ...
0
votes
0answers
46 views

Can an index be added to a PRIMARY with a null key?

I have a query that takes a long time to return, roughly 12-15 seconds, which I'd like to optimize. The query below is the result of running the original query with SHOW WARNINGS. If I run EXPLAIN ...
0
votes
0answers
26 views

How can I optimize a query with multiple left joins?

The working example that I have been provided with takes about 20 minutes to fully process. http://paste2.org/G8BZvNHe What are some basic strategies for making this more efficient? Edit: I've ...
0
votes
0answers
40 views

MySQL Query Causing Massive Slowdown Over Time

I've been experiencing massive slowdown on my server after launching a new website. Using MONyog I can see that after an apache/mysql restart, the queries/second starts out nice and low for around 10 ...
0
votes
2answers
60 views

What indexes should one use to optimize a PostgreSQL query with a JOIN depth of 2?

Disclaimer: I am relatively new to PostgreSQL. I'm wondering how to optimize a query that does 2 INNER JOINs. My scenario is fairly simple: Select Posts with a photo (Posts.photo IS NOT NULL) and a ...