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)

1
vote
0answers
57 views

Is there a way to improve this query further?

I've got a query that over time I've been improving. Originally it simply selected * from the records table, and the data was formatted on the webapp side. But that was slow and I wanted to make ...
2
votes
2answers
139 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 ...
0
votes
0answers
39 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
26 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 ...
2
votes
0answers
55 views

Performance differs on same query against two (almost) identical tables?

I'm running these two queries in SQL Server 2012: SELECT tpua.Field1, CAST(round(twg.Field2 * 2, 0) / 2 As Decimal(8,1)) As Field2, CAST((round((twg.Field3/10.0) * 2, 0) / 2) * 10 As int) As ...
0
votes
1answer
52 views

Replace nested “select with count” with outer join? is this possible?

SELECT c.classid , c.classname , (select count(s.id) from students s where s.classid = c.classid and status = 'failed') as noOfFailed, (select count(s.id) from students s where ...
1
vote
0answers
46 views

SQL - Optimize table population with dynamic queries

I need to optimize the following pl/sql procedure. So, fundamentally my question is: There is a way you can do a bulk insert in a pl/sql that could be more efficient than loop through an associative ...
1
vote
1answer
68 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 ...
0
votes
2answers
40 views

Is this query supposed to be slow?

Select * from (select tmp.userid userid, tmp.username ,tmp.likescore+tmp.commentscore+tmp.sharescore+tmp.tagscore+tmp.userscore+tmp.lsharescore+tmp.friendscore as score from ( SELECT ...
0
votes
1answer
153 views

What is the most efficient way to count the number of rows in a table?

I am using postgres with the following query: select count(*) from image; The primary key on this table is non-incrementing; it's a unique serial number for the images stored in the table. Our app ...
0
votes
2answers
66 views

Joining 4 tables and selecting distinct values based on 3 timestamps

I have been trying to remove duplicates from my query results based on the time but I would need to consider 3 different times and i am not sure how to do that. As some of the testing happen multiple ...
0
votes
0answers
48 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 ...
0
votes
0answers
69 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
1answer
37 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" ...
1
vote
1answer
64 views

What is causing an INTERSECT to run forever when the individual SELECTs run quickly? [closed]

I have a script that applies the INTERSECT operator to 2 separate queries. Query1 executes instantly and returns ~1k rows. Query2 executes almost instantly and returns 200k rows However, when ...
0
votes
1answer
36 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 = ...
0
votes
1answer
79 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
201 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 ...
2
votes
1answer
260 views

Optimise select distinct from large table

I have a table that contains 320,071,712 number of records, the report that I am currently working on can filter the records by date, after which the number of records come down to 145,878,852. The ...
0
votes
1answer
44 views

Optimizing a MySql query

Here's the query: set @_YEAR = 2014; set @_MONTH = 1; set @_PRODUCTS = '53'; set @_START = date(@_YEAR * 10000 + 1 * 100 + 1); set @_END = date_add(date(@_YEAR * 10000 + 1 * 100 + 1), interval 1 ...
1
vote
1answer
92 views

Multi table GROUP BY query optimisation

Currently I have a multi table/column GROUP BY query which is pretty slow. Below is the tables schema used by the query: report Table ...
2
votes
0answers
22 views

Query optimization - contact should be extracted at most once for a particular customer

I have created a database for a telemarketing company Scenario is, the telemarketing company is selling contacts to the customers. Customers log on to the website and search for the contacts for ...
2
votes
2answers
78 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
40 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 ...
2
votes
2answers
192 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 ...
3
votes
0answers
208 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 ...
1
vote
2answers
149 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
101 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 ...
2
votes
1answer
532 views

Find out beforehand how many records a query has

I develop reports in ExtJS resulting of queries in SQL Server 2012. I use Ext.Grid with paging, therefore I can use offset to limit the amount of records a query will return. But for this ExtJS ...
0
votes
0answers
40 views

Issues with Optimizing TSQL Script

I have a temp table that has a SecurityCode column. This SecurityCode column gets updated based off value from a CrossReference table. The value selected from the CrossReference table depends on how ...
3
votes
2answers
225 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
1answer
22 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 ...
1
vote
1answer
38 views

How to get paged resultset from a table?

I am trying to get paged resultset from a table (Notes). So, I am passing the last offset id (@LastOffset) value along with the page size (@Count). My approach to achieving this is: Sort the table ...
0
votes
0answers
32 views

SQL Server 2012: query to see queries that are consuming more resources [duplicate]

I'm trying to find/build a query to show me those queries that are consuming more resources in SQL Server so that I can optimize them or at least have a statistical idea of what is really happening. ...
1
vote
1answer
98 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
28 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
52 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
228 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 ...
0
votes
0answers
28 views

Creating separate small table from big based on access frequency for “select” performance

I have table with several millions of rows which are selected a lot (many times in a second in average). It is indexed and everything is ok for now. But performance is always a question (sometimes ...
1
vote
2answers
88 views

Slow subquery in SELECT statement

Having tried many different methods, I can't get my query taking shorter than 40 seconds to run with a dataset of ~11000 rows. The table is a list of hotel bookings, containing (most relevant) ...
1
vote
2answers
152 views

mysql multiple column index Cardinality order

How does Multiple-Column Indexes work in MySQL? How is it stored internally? consider a table with Multiple-Column Indexes on two columns (X,Y). If X has High-cardinality than Y i.e., X is less ...
1
vote
1answer
204 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 ...
6
votes
2answers
863 views

Can rebuilding indexes cause worse performance after the rebuild is finished?

We have a customer database that is heavily fragmented - practically every table with more than 1000 pages has >95% fragmentation. Fill factors are set to sensible values, but page space usage is ...
1
vote
2answers
61 views

Get the pais_id not associated to fabricante_distribuidor_id and producto_solicitud_id, the opposite from SELECT query

I have this query for get all the pais_id given a fabricante_distribuidor_id and producto_solicitud_id, the query works fine. SELECT DISTINCT nom.pais_id FROM negocio.fabricante_producto_solicitud ...
0
votes
1answer
48 views

optimizing a select query of 1.5 million entry

I have the following query: SELECT s0_.id, s0_.lowresimageurl FROM app_instagram_shop_picture s0_ FORCE INDEX ( idx_deleted_shopId ) LEFT JOIN app_instagram_shop s1_ ON s0_.shop_id = s1_.id AND ( ...
0
votes
0answers
44 views

Best mysql database structure for this data

I need to create a MySQL database that can store the following data for many users: General settings (for each user) 10 url's. 10 words of 20 letters for. ...and more fields like these. Sentences ...
1
vote
1answer
55 views

MySQL column type to boost performance

I'll have a table where: userid (link to user's id in users table) foldername (a varchar, max. 260 char) entry (varchar, max. 1024) data (varchar, max. 20kb) Single user will have several ...
1
vote
1answer
139 views

optimize design for a table with 50 columns [closed]

I have a table with 50 column and one of my friend has told me to split the table in 4 different table to save 12 column in each table. He proposed a design to have Master table and refer the PK of ...
0
votes
0answers
63 views

Large text column slows down whole table

I have a mysql database table table1 with a dozen columns in it. One column is a mediumtext column with lots of data in it and all the other columns are small (ints, timestamps, etc.). The presence ...