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)

0
votes
1answer
167 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
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
2answers
68 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
50 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 ...
5
votes
4answers
3k 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
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" ...
0
votes
0answers
71 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 ...
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 ...
8
votes
5answers
851 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
37 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
80 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
211 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
267 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 ...
1
vote
1answer
69 views

optimizing query for slow queries

I have this query in my slowqueries table which essentially is taking about 5-8 seconds to complete the query time: SELECT s0_.id AS id0, s0_.updated AS updated1, s0_.created AS created2, ...
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 ...
0
votes
1answer
89 views

How to prepare executon plan for given sql query on a server with 4 processors (Oracle database)

4 processors means the query runs in parallel. Select /*+ parallel(4) */ T1.A FROM T1,T2,T3 WHERE T1.A = T2.A AND T1.B=T3.B AND T1.C=T3.C Tables T1,T2 and T3 have 4,8, and 16 partitions ...
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 ...
1
vote
2answers
123 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
79 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 ...
3
votes
0answers
211 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
228 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
154 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 ...
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 ...
2
votes
1answer
558 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 ...
1
vote
0answers
102 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
23 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 ...
26
votes
1answer
4k 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
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
235 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
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 ...
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 ...
6
votes
2answers
881 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
1answer
140 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 ...
5
votes
2answers
183 views

Multi-row insert vs multiple single row inserts

In my app I do multi-row inserts when I can just because it reduces the number of round trips between the db and the app. However, I was curious, is there any other advantages? For example, if ...
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
46 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 ...
0
votes
0answers
64 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 ...
2
votes
1answer
145 views

Threshold for sort spill and random cardinality estimates

I wanted to test tempdb spill warnings so I run the following script on SQL Server 2014: USE tempdb IF OBJECT_ID('tempdb..tblTest') IS NOT NULL DROP TABLE tblTest CREATE TABLE tblTest ( ...
0
votes
0answers
181 views

How to Insert records from remote database to local database using dblink in postgresql 9.3

I have a scenario like i have a two DB, one resides in remote server and other resides local. Now i have to fetch the records from remote DB when new records are inserted on tables (in remote DB) and ...
0
votes
0answers
222 views

Database design for price comparison site - too complex?

I'm working on a regular price comparison site with two exceptions. I want to show price history and also allow users to subscribe to a product with a specific size and color. As a result, I've ended ...
1
vote
0answers
74 views

Delete Query throwing warning db2

I am trying to delete multiple records from view The view has Instead of Trigger defined .. The view has records consolidated from multiple tables. [db2inst1@testServer001 ~]$ db2 "delete from ...