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 (3)

0
votes
1answer
23 views

Is it possible to perform skip N rows and fetch M rows without full scan

Let's say I've got this query: SELECT EMP_ID,LAST_NAME FROM EMPLOYEES WHERE POSITIONID IN (1,3) ORDER BY POSITIONID,LAST_NAME; Now if I want to show only the first ten rows between 50 and 60, ...
1
vote
1answer
83 views

Why using where in indexed field is slow?

we have a big table. the table structure is : CREATE TABLE `visit_logs` ( `id` int(10) NOT NULL AUTO_INCREMENT, `user_id` int(10) NOT NULL, `ref_id` int(10) NOT NULL DEFAULT '0', `link_id` ...
0
votes
0answers
7 views

Are Access path, Access method and Physical operator synonyms ?

I'm confused regarding some concepts related to Database optimization which are : Access path, access method and Physical operator. Let's consider a Join operation, to my understanding, this logical ...
1
vote
0answers
36 views

Could auto_analyze cause inconsistent (and bad) query plans?

I have a simple table which stores metadata about users in an hstore column. This table has ~1.1M rows. Table "public.stitcher_participant" Column | Type ...
7
votes
3answers
258 views

Execution Plan is NOT using INDEX, It uses Table Scan

I know when it comes to using an index or a table scan, SQL Server uses statistics to see which one is better. I have a table with 20 million rows. I have an index on (SnapshotKey, Measure) and this ...
0
votes
1answer
54 views

Phpkb Query Optimization

Running a phpkb CMS on a ubuntu lamp server. mysql - 5.5.28-0ubuntu0.12.04.3-log The tables in this database are not large in fact I have not found one over 10k rows. Every five min I get a 'copy ...
0
votes
1answer
81 views

Why did a query change its execution plan and how to anticipate the change?

We have a job using a dynamic query in production for years now and its execution took 5-6 seconds up until our last code deploy. Now the same query takes days to finish. It's a relatively simple ...
0
votes
1answer
21 views

How to support custom fields for an entity and list them as columns?

I am currently working on a system which supports "custom fields" for users. I will illustrate this with a simplified example of this database schema: users -------- user_id name age custom_fields ...
0
votes
1answer
27 views

Schema size [PG TOAST] - PostgreSQL 9.2

I've got the following query to measure the schema's size on my DB: WITH schema_size AS ( SELECT tab.table_catalog AS database_name, tab.table_schema AS schema_name, tab.table_name, ...
1
vote
1answer
37 views

how to clear only the query plans from the current session?

I am working on tuning a query on a very busy server, where I don't want to clear the cache, using DBCC FREEPROCCACHE; I know it will cause at least a CPU spike, and in this case I don't want it. ...
1
vote
1answer
38 views

Slow Query Postgres 9.2

I've got the following query: SELECT j.id, concat(c.company, ' ', c.name_first, ' ', c.name_last) AS client, c.email AS client_email, concat(bc....
0
votes
2answers
46 views

What are the SET options that affect Plan Reuse and how to get their values in T-SQL?

I'm troubleshooting the case when a stored procedure runs great from the management studio at any time, but the same stored procedure can run terribly badly in one of the webservers, even for the same ...
2
votes
2answers
75 views

Filtering a result set on a very large table (using INCLUDE index in SQL Server)

Consider a very large table having hundreds of millions of rows defined as -- Here ObjectIdName and ObjectTypeName together are unique. -- Due to their size they aren't indexed, but rather they are --...
7
votes
3answers
153 views

How to improve estimate of 1 row in a View constrained by DateAdd() against an index

Using Microsoft SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (X64). Given a table and index: create table [User].[Session] ( SessionId int identity(1, 1) not null primary key CreatedUtc ...
1
vote
1answer
36 views

Does TokuDB need any regular cleanup/maintenance?

PostgreSQL has vacuum and InnoDB has purge. Does TokuDB have any required maintenance/cleanup operations? I couldn't find anything in the online docs: https://www.percona.com/software/mysql-database/...
2
votes
2answers
96 views

Eliminate duplicates and optimize performance for big count and small LIMIT

I have a big query in my Postgres 9.1 database: SELECT *, count(*) OVER () AS full_count FROM ( SELECT DISTINCT ON(message.messagetime,message.messageid) message.messagetime, ...
3
votes
3answers
67 views

IN Clause - listing values VS query

My app runs a lot of queries like this SELECT ... FROM Table WHERE CategoryID IN (SELECT CategoryID FROM OtherTable WHERE ..) To save performance I decided to cache the second subquery in the ...
-1
votes
1answer
80 views

Filter first then join or join first then filter?

Consider 5 tables with more than 1 billion records each. A query needs to join them. And I know that less than 10% of their records will be require. Say they all have a Date dimension, and only data ...
3
votes
0answers
63 views

Optimizing an Intersect query between two huge spatial tables

I am having a hard time trying to improve an intersect between two spatial tables and I would like to receive any tips about the table designs, queries or dba configs. Tables: Table "teste....
2
votes
1answer
92 views

Optimize performance for query with CTE using a multicolumn index

I've got this slow query: WITH flks AS (SELECT id FROM public.ja_feedlog WHERE gtime BETWEEN EXTRACT(EPOCH FROM TIMESTAMP '2016-01-24')::BIGINT AND EXTRACT(EPOCH FROM ...
2
votes
1answer
55 views

Set a filegroup to readonly

I have a set of dimensions which are updated only during specific times. I have moved these tables to a specific filegroup named Dimensions. I wanna keep this filegroup as readonly. When ETL runs, it ...
0
votes
1answer
32 views

Php, MySql - Optimisation

I asked this on the general site, but they suggested I come here for ultimate optimisation. It really boils down to which is faster; doing a load of calculations in php with database stuff that is ...
1
vote
2answers
68 views

How to avoid full table scan when using OR operator on an Index

Essentially, I want to get intersection between 2 tables based on matching IDs with the option of wildcard '*' to return all IDs: Select * from A a inner join B b on b.id = a.id or b.id = '*' ...
1
vote
1answer
48 views

Reuse subquery as in another result

In a typical social-network-like application I have the following query that gathers news from a set of followed channels. The main query gets the news, while the subquery gets the followed channels: ...
11
votes
3answers
252 views

Why is my EXISTS query doing an index scan instead of an index seek?

I am working on optimizing some queries. For the query below, SET STATISTICS IO ON; DECLARE @OrderStartDate DATETIME2 = '27 feb 2016'; DECLARE @OrderEndDate DATETIME2 = '28 feb 2016'; SELECT o....
0
votes
1answer
35 views

Which columns to index with LEFT JOIN and GROUP_CONCAT

So i have a complicated MySQL search query, and I wonder which columns I need to index to speed up searching: SELECT wpp.ID, post_title, wp_terms.name AS category, wp_terms.slug AS slug, ...
0
votes
0answers
41 views

How to make sure vacuum is happening in postgres sql db

I have created a table with two columns and created index for the first column. Then inserted 2 million data using generate series function for first column (2nd column is having null). And executed ...
2
votes
0answers
43 views

Postgres planner overerstimate index only scan

\d hits; Column | Type --------------+----------------------------- id | integer link_id | integer ...
1
vote
1answer
174 views

How can I efficiently optimise MariaDB?

I am running a 24 GB KVM with 8 Xeon 3.2 SSD RAID 0. And I would like to get the most from my MariaDB sql server. I am using following my.cfg # # This group is read both both by the client and the ...
4
votes
2answers
47 views

A lot of ad-hoc queries impact on server

We have a third party application which generates a lot of ad-hoc statements (our server has 16GB of memory and there are 140000 ad-hoc queries present in the cache).I've enabled 'Optimize for Ad-hoc ...
1
vote
0answers
17 views

Very different query plans with and without LIMIT in PostgreSQL.

I have the following query which I am using to create vector tiles for a web map. I'm pulling together just the geometries within the selected tile using a CTE, then joining it to get additional ...
0
votes
1answer
34 views

Query not using indexes

I have a query which is not using the timestamp index and scanning 200 million records. I know that the query will not use the index if there is a function applied on top of it. However is there any ...
2
votes
1answer
64 views

Why is index not used for IN expression with unnested array

I have a query that joins two tables but refuses to use use index unless I add a limit. The case is a bit complicated so best described by showing you. I have two tables, users and ...
3
votes
1answer
101 views

Optimizer gets the wrong number of estimated rows even with updated stats [closed]

I have a procedure running in SQL Server 2008 that contains a query similar to the one below; SELECT /* various columns from table 1 and table 2 */ FROM dbo.tbl_1 AS a INNER JOIN dbo.tbl_2 AS b ON a....
0
votes
1answer
180 views

Huge database… queries too slow. How to improve performance? [closed]

I'm currently managing a MySQL database with 30 million rows(many more to come). The entire database is 280GB. The server has 4GB of RAM and 3.2Ghz 4 core processor. The largest column stores an array ...
0
votes
0answers
27 views

Query slow on 5 Mil fact table

I have a fact table with currently 5 mil rec. The record count will go up as ETL runs on daily basis. The below reporting dashboard query is taking 1.50 sec to execute. There are many number of ...
0
votes
0answers
46 views

Recursive CTE Optomization

I have the following rCTE used in several different queries to determine the requesting user's place in the hierarchy. Buyer is the User who is purchasing. ItemID is the thing that they can purchase,...
0
votes
1answer
67 views

If I query records matching some value, why does InnoDB examine most of the records that had that value once, but have changed since then?

We have an issue with MySQL InnoDB performance. We insert records into a table with a status field value of zero, these records need to be processed by another task. When this another task starts ...
3
votes
1answer
484 views

Improving select query

I was at an interview for a new job today and got asked a question regarding a SELECT query: SELECT lastname FROM users WHERE name='John'; This SELECT query is to run over a million rows. I was ...
1
vote
1answer
70 views

SELECT query types and optimization

I'm using a database monitoring system that, amongst other data, shows the following values for SELECT queries: number of joins that use ranges on the first table number of joins that do a full scan ...
1
vote
1answer
78 views

Optimize Row Level Security expression in Postgres

I have a table which contains millions of rows, categorized into a few categories (lets say numbers 1-5). An application that accesses the database uses one single database account. However, the ...
1
vote
1answer
34 views

MySQL indexing issue when trying to search for a part of a string/word

What we're running into: Engine: MySQL. We've been working on a filter where an user can search for a reference number. However, we've ran into an issue with performance on bigger data sets. We ...
1
vote
1answer
84 views

MySQL difficulty with optimizing slow query with index but without improvement. Explains tells otherwise

I've a slow query (~11 seconds) that I'm trying to optimize. But the use of indexes seems to not make any difference even thou Explain indicates otherwise. Let me walk you through my problem. So this ...
2
votes
1answer
47 views

Does the query optimiser re-use usable data?

This question is to expand my knowledge on how the query optimizer of SQL Server works. I like to know how stuff works behind the scenes so I can better optimist it/debug it. I have the following ...
0
votes
0answers
65 views

How does the PostgreSQL query planner estimate the aggregated rows count?

During an SQL optimization session I ran into the following problem (I won't post the full query and its plan as they are quite large): The plan: ->__SOME_OTHER_NODES__ -> ...
1
vote
0answers
83 views

Very slow MySQL performance

I have 4 million rows in MySQL. I have a count query to fetch from DB to Apache web server. The issue is first load (Count query) - when I click fetch button, it takes more than 5 mins. Second time ...
2
votes
1answer
84 views

Would a cron job to automatically optimize tables on a monthly basis be ideal?

I'm wondering if running the OPTIMIZE command via cronjob would be an intelligent move? Sometimes I forget to run optimize for months at a time. My database is frequently using select, insert, update....
0
votes
0answers
60 views

Optimize MySQL JOIN Query

I've been using following Query to fetch data from MySQL. SELECT SQL_CALC_FOUND_ROWS DISTINCT (wi_individual_g.ind_id), wi_individual_g.ind_id, wi_individual_g.ind_name, ...