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
1answer
412 views

MySQL UPDATE query stuck on “Sending Data” State

I am having difficulty getting an UPDATE query to successfully execute, due to the process getting stuck on "Sending Data" state. I feel this differs from similar questions about the "Sending Data" ...
0
votes
0answers
30 views

Query performance tuning with joins on views with large data

I've this query in oracle. This is basically the query of a BA report which I'm trying to tune. SELECT DIM_PROJECT_OL.PROGRAM_ID, DIM_ANALYTIC_STRUCT_ACCOUNT_OL.STRUCTURE_ID, ...
0
votes
1answer
46 views

Is there any better way in oracle to enable parallel other than this?

I want to do do following set of operation. execute immediate "alter session enable parallel query"; execute immediate "alter session enable parallel ddl"; execute immediate "alter session set ...
0
votes
0answers
64 views

how to minimize the effect of updating statistics on the plan cache?

The issue I am trying to address: After updating statistics there are slow downs in running many of our processes here. then following the scary dba on this link below: ...
11
votes
2answers
282 views

Why do SQL Server row estimates change when I add a join hint?

I have a query that joins a few tables and performs pretty badly - row estimates are way (a 1000 times) off and Nested Loops join is chosen, resulting in multiple table scans. The shape of the query ...
2
votes
1answer
490 views

Mysql Optimization suggessions

I have an InnoDB database table with more than 2 million rows. I am running efficienty indexed queries to reduce server load, but site becomes slower day by day. I ran Mysqltuner.pl to identify any ...
2
votes
1answer
305 views

Help required with query (join) optimization

I am working with a fact table source query and I have observed that performance of the query is pathetic. It has increased from 1:00 min to 6:30 min by just using a function in the select clause ...
1
vote
2answers
327 views

How to lower “Created_tmp_disk_tables” - temporary tables count created on DISK?

I have a ubuntu, mysql 5.6 DB that is heavy used by thousands of users. After 10 days of running I have this values: | Created_tmp_disk_tables | 894170 | | Created_tmp_files | 26068 | | ...
1
vote
3answers
67 views

Why does the query plan still sort a table despite having an index sorted on the columns?

I'm using Postgres 9.1 I have two tables I am joining: wikidb=> \d page Table "public.page" Column | Type | Modifiers ...
1
vote
1answer
267 views

query performance gains by removing operator hash match inner join

While trying to apply the contents of this question below, to my own situation, I am a bit confused as how I could get rid of the operator Hash Match (Inner Join) if any way possible. SQL Server ...
1
vote
2answers
113 views

Multiple-View Query Optimisation

We have a monster view that contains joins to many (many) tables in order to return the data that is required. In some cases, to get a column of information, we've have to join onto 4 tables, just to ...
2
votes
1answer
46 views

Tuning a query with: index, window function?

Description Environment: Centos 6-x64, Postgres Plus Advanced Server 9.3. I have a table with 4 columns with data as below: id code_id effective_date name 24 12 ...
11
votes
1answer
107 views

Why do these similar queries use different optimisation phases (transaction processing vs quick plan)?

The example code in this connect item Shows a bug where SELECT COUNT(*) FROM dbo.my_splitter_1('2') L1 INNER JOIN dbo.my_splitter_1('') L2 ON L1.csv_item = L2.csv_item Returns ...
2
votes
1answer
103 views

Understanding performance of highly unique WHERE clauses

I've been struggling to understand how to deal with a specific type of performance issue that shows up very often in this scenario: when you are wanting to apply multiple filters in a query, but you ...
2
votes
1answer
139 views

Two updates cause a deadlock

I have a mysql queue with the following sheme: CREATE TABLE IF NOT EXISTS `media` ( `mid` int(11) NOT NULL AUTO_INCREMENT, `order_type` int(11) NOT NULL, `media_id` varchar(50) COLLATE ...
2
votes
1answer
75 views

How to tune the query on all_constraints in Oracle 12c?

How to tune the following query, as it's taking around 8 seconds: select constraint_name,table_name from all_constraints where r_constraint_name in ...
1
vote
1answer
50 views

Query optimization in case of TIMESTAMPDIFF

I have a table where I need to delete all rows older than 5 hours. Currently I'm doing: delete from my_table where TIMESTAMPDIFF(HOUR, from_unixtime(round(created_on/1000)), now()) > 5 The ...
1
vote
1answer
57 views

how to get the missing indexes that are “filtered indexes”

I have a procedure that shows me the missing indexes. It has been very good to me, but it does not show me the missing FILTERED INDEXES. I would like my procedure to be able to catch and suggest ...
1
vote
1answer
297 views

Is there a way I can shorten the below query into a single query?

I have the below query. IS there a way I can put this into one single query? If not, can I shrink it further anyway? Please advice. DECLARE @highRegion TABLE(regionId INT, countR INT) DECLARE ...
11
votes
2answers
918 views

Will a WHERE query do checks on simpler comparisons (i.e. bit) before running more arduous comparisons (i.e. varchar)?

If I write a query that includes a compound WHERE clause, e.g.: SELECT * FROM MyTable WHERE BitField = 1 AND VarcharField = 'asdf' and the inclusion of that bit comparison simply excludes the ...
0
votes
3answers
136 views

Optimize mysql --> 'show global status'

I am working in the optimization of a MySQL Wordpress database running in a RDS instance at Amazon AWS. The instance is a t2.medium machine with 4GiB of RAM and 2vCPU. The size of the DB in a sql file ...
1
vote
1answer
30 views

Database: what design to have a “document table” referencing others tables

I have some tables which can have 0..* documents. How can I design my data model to avoid having a specific document table for each of my tables? Feel free to suggest by editing the data model
3
votes
0answers
158 views

Form builder schema for SQL database?

There are many ways one can envision the solution, from the top of my head: Two table approach: Field table with (name, field_name, type, description, meta) attributes Form table with (name, ...
2
votes
2answers
76 views

MariaDB server with 80K-100K DataBases

BACKGROUND : We have an environment where there is a new DB created almost every minute and it will be Dropped after few days or weeks (maximum 1 month) such that the space used is about 300GB~400GB ...
1
vote
2answers
125 views

Optimization of variables in my.cnf (mysql) for Drupal

I just get a Dedicated Server running UBUNTU and I need to optimize the variables in my.cnf for Drupal 7. I understand that this is tailed job. Then Is there any tool to help me to do this? I am a ...
3
votes
1answer
92 views

4m Rows in MySQL, ~5 Minutes to Run Query

I have 2 tables, a users table and a log table which literally logs each time a user signs in. What I'm trying to do is grab a list of all users with a certain rank who have not logged in for over a ...
1
vote
2answers
61 views

How to optimise MySQL query that uses dependent subquery

I have a web app that has a custom feed of images. The image queried are based on whether the users have previously seen the image and ordered by a Hacker News style score. There are only 1000 rows ...
0
votes
1answer
37 views

Is it advisable to asynchronously update the summary table every time a transaction is deleted?

Given three tables: (a) Product (b) Transaction Head (c) Transaction Detail We need to create a query that fetches a list of products with associated latest ordered quantity and latest transaction ...
2
votes
2answers
54 views

Optimizing query using a WHILE statement

I'm trying to convert a view that extracts a yearly amount into something more WHILEish The query is more or less: SELECT * FROM (SELECT SUM(EmployeePaid)*2 AS Total2013 FROM TblRecords WHERE FYear ...
2
votes
1answer
64 views

How to merge partitions in Postgres?

I have a table that is partitioned by date on a monthly basis beginning January 1, 2014. There is significantly more rows of data in 2015 than there were in 2014 and the data in 2014 is not accessed ...
5
votes
1answer
84 views

Joining in query decomposition

I have a question about query decomposition and localization, as in here: When joining 3 tables (here EMP, ASG and PROJ) is there any specifiy order, when to join which table? Why ASG and EMP are ...
3
votes
2answers
90 views

How does TOPN influence a query with group by?

I have a query that looks like the following: Select t1.field1, t2.field2, sum(t1.field3) From t1 inner join t2 on t1.id = t2.id Group by t1.field1, t2.field2 It's simplified, normally there are a ...
2
votes
2answers
109 views

MySQL: memory requirements for django app with 1 big table (460M rows)

I have created a django app that is running on virtual machine. All db tables are rather small (up to 1M rows) but recently I was asked to add a big table (460M rows). CREATE TABLE `genescorrelation` ...
5
votes
2answers
95 views

Will I need to create a pivot or can I alter this query

The following Query that I wrote is working to a point. The problem that I found is that in each pump, cable, etc can be listed in each of the part columns. Example: What I would like to get is ...
0
votes
1answer
67 views

Oracle 11g PGA advice view is empty when it shouldn't

I have the following situation in an Oracle 11g server (it's part of a RAC). PGA_AGGREGATE_TARGET is set, STATISTICS_LEVEL is TYPICAL, and yet V$PGA_TARGET_ADVICE shows up empty. Why does this happen? ...
0
votes
2answers
73 views

Optimize query or split in two queries and process result separately?

I have this DB schema: As you may notice already there is no relationship between tables and won't be on the future. If I run the following queries I will get a lot of rows per table: SELECT ...
2
votes
2answers
320 views

Why the WHERE clause is not pushed down in the view's query?

With Postgres 9.4, I'm doing the following query quite often: SELECT DISTINCT ON(recipient) * FROM messages LEFT JOIN identities ON messages.recipient = identities.name WHERE timestamp BETWEEN timeA ...
2
votes
1answer
126 views

How can I optimize searching a column for a sub-string in SQL Server?

I have a very basic table: CREATE TABLE [obj_local] ( [obj_id] INT NOT NULL, [value] NVARCHAR (1000) NOT NULL ); This table stores a lot of data, and I need to search ...
4
votes
2answers
971 views

Does an empty column value occupy same storage space as a filled column value?

I have a table with 2 columns. The type of both columns is set to varchar(38). If I create a row with an empty value for one of the columns, will it take same storage space as if the value was not ...
11
votes
3answers
299 views

How can I learn more about how SQL Server “normalizes” queries prior to creating the execution plan?

Looking at "SQL Server 2008 Internals"1 by Kalen Delaney, on page 13, it states the following: "The first step in producing such a plan is to normalize each query, which potentially breaks down a ...
0
votes
0answers
89 views

Performance Issue on Centos Server (Mysql)

I have performance issue on my centos server (vps). I asked about it in serverfault and I was said there is a problem with mysql and ask here. Here is serverfault topic, you can find details. ...
1
vote
1answer
63 views

Is smallest to largest the rule of thumb in a multi-table JOIN?

Given this query, SELECT a.* FROM a LEFT JOIN b ON b.a_id = a.id LEFT JOIN c ON c.a_id = a.id HAVING COUNT(DISTINCT b.id) < COUNT(DISTINCT c.id) if b is a significantly larger table than c, will ...
1
vote
1answer
243 views

MySQL Query with good execution plan is very slow due to Sending data operation

I have the following query: SELECT a.borrowerId AS borrower_id, a.created, NULL AS initiator_team_id, NULL AS initiator_user_id, NULL AS old_team_id, NULL AS old_user_id, ...
2
votes
1answer
57 views

How to optimize the sort in Postgres query

I've been trying to optimize the sort in the following query. I ran EXPLAIN ANALYZE and the majority of the time is during the sort when it arranges the output by distance. I've tried converting the ...
1
vote
0answers
10 views

PostGIS Intersects Query takes lot of time to get the result

I am developing a webmapping application in which I used PosgreSQL with PostGIS plugin as my DB. I have this two tables namely; evidensapp_polystructures and evidensapp_seniangcbr. Both tables ...
0
votes
2answers
89 views

How to improve perfomance or Query Execution Time in MySQL?

This is my query: SELECT a.z_companyid_pk "z_companyid_pk" , a.company_name "Client" , a.display_name "Display_Name" , ...
1
vote
0answers
44 views

Oracle tuning strategy for columns with long tails

We have an Oracle 11g database, where the two biggest tables are Customer and Order. The order profile of our customers has a long tail: we have millions of customers, with most only having a small ...
3
votes
1answer
79 views

MySQL query WHERE IN + ORDER BY, which EXPLAIN is better and how to avoid filesort?

This is my table definition, without any special index for now: CREATE TABLE `filter` ( `field_name` varchar(32) COLLATE utf8_unicode_ci NOT NULL, `category_id` varchar(32) COLLATE ...
3
votes
2answers
202 views

SQL server tools to identify query optimisations and missing indexes [closed]

I have a broad question regarding SQL Server that was given to me on a quiz and I am not sure what the answer is as I do not have access to SQL Server at home. My question is how can I utilise SQL ...
1
vote
1answer
68 views

Int comparison very slow in query

I have this for simplicity fictional table which explains my problem: CREATE TABLE IF NOT EXISTS `posts` ( `pid` int(11) NOT NULL AUTO_INCREMENT, `user` int(11) NOT NULL, `posts` int(11) NOT ...