Questions tagged [optimization]
In the context of a database, optimisation refers to the process of the query optimiser selecting an efficient physical execution plan.
364
questions with no upvoted or accepted answers
8
votes
0
answers
907
views
How to minimize access exclusive table locks while reducing bloat or ALTER (ing) a table?
In some circumstances, I've been told not to do a VACUUM FULL (or CLUSTER) of a table that is in production, because that will exclusively lock it for longer than wished. The same might apply to ...
- 13.3k
6
votes
0
answers
3k
views
Slow Postgresql query with left join
I have a query on Postgres, and I also have added proper index. Is there anything missing here?
SELECT orders.*, demo.name as d_name
FROM orders
LEFT JOIN users as demo ON demo.id = orders.dr_id
...
- 161
5
votes
1
answer
7k
views
Materialized Views and Indexes
Is there any way to force PostgreSQL to use an index that I created on a materialized view table? I tried every combination possible of moving the column attributes around to make Postgres to use my ...
- 61
5
votes
1
answer
4k
views
SQL Server performance issue (CXPACKET wait type)
I have a performance issue with production SQL Server 2012 which has 24 cores and 32 GB of RAM. From wait type I can see CXPACKET is on the top based on the following SQL Server metrics, should I ...
- 409
4
votes
1
answer
2k
views
Slow postgres query that uses quick sort instead of hash-aggregate
I'm on Postgres 9.5 and working with a web analytics database that records visitor traffic.
I'm trying to optimize a very slow query that gives me a count of the unique people that visited a given ...
- 1,074
3
votes
0
answers
72
views
Is there any way to optimize this query (generated by Drupal)?
I have an older system built on drupal, and there is a particular (generated) query that takes a long time - never completing in some cases.
The query:
SELECT
node.nid,
node.created,
node....
- 1,202
3
votes
1
answer
319
views
How to optimize simple table with groups and timestamps (SUM over 15M rows with GROUP BY)
I think I have quite simple question, but I couldn't find the answer anywhere.
I'm using MySQL and I have a simple table:
id
timestamp
groupId
costA
costB
costC
...
1
2022-02-01 19:45
1
5,13
3,20
30,...
- 31
3
votes
0
answers
89
views
Poor query planning in Postgres 10
The query planner in Postgres10 seems to be ignoring the fact that my tables are clustered. I have to explicitly put "hints" in my query to have it perform the correct type of join.
Here's my ...
- 131
3
votes
0
answers
729
views
Searching between two BIGINT
I have a table defined as:
CREATE TABLE [dbo].[IPS_INTEGERS](
[id] [int] IDENTITY(1,1) NOT NULL,
[network_start_integer] [bigint] NULL,
[network_last_integer] [bigint] NULL,
[...
- 39
3
votes
1
answer
3k
views
How to optimize a Full Text Search
So we have this VARCHAR(255) column called code, which is a string of varying size we use in a WHERE clause.
Doing this search slows the query down by about 300ms and since the query is run a few ...
- 294
3
votes
1
answer
777
views
MySQL using variable in SELECT command with view affecting performance
sorry if this has been asked already. I've just moved from MySQL 5.6 to 5.7.17 and I'm struggling to understand this issue.
I have a view "delete_me_test_" which has around 26k rows.
SELECT
...
- 131
3
votes
0
answers
6k
views
Optimize long LEFT OUTER JOIN query
I have query which has 40+ left outer joins. Query is not that slow but I'm interested that can I optimize this (procedure is most used one in db). I had idea that "a" table has type column which ...
- 303
3
votes
2
answers
48
views
How to avoid inner query im MYSQL
I have a table with 4.7 million rows and I am executing this query on this table:
SELECT * FROM students WHERE `eid` IN
(SELECT DISTINCT `eid` FROM students WHERE code IN ('120', '330'));
This is ...
- 31
3
votes
0
answers
102
views
Understanding Database Query Cost Optimzation
I have been learning Query Optimization from "Fundamentals of Database Systems by Elmasri and Navathe (6th edition)" and I am having trouble understanding on how some variables are being derived in ...
- 39
3
votes
1
answer
124
views
PLSQL using cursors or functions to query repeated data
Is it better to declare a commonly used lookup query as a package function or as a function (either returning rows or values or returning a sysref cursor)?
Whats the difference?
What are the best ...
- 131
3
votes
0
answers
657
views
How to optimize update using join in postgresql
I'm using PostgreSQL 9.3.5 on a CentOS system. I have a very large table (twenty million rows, 50+ column) that aggregates data from a few hundred systems. Several times a day each of the systems ...
- 51
3
votes
0
answers
83
views
Tagged questions
I have similar to StackExchange network that utilises tagged questions.
In my web application Users also can find questions by tags. The query below returns a set of tagged questions:
SELECT ...
- 151
3
votes
0
answers
450
views
Postgres - Partitioning old tables, partition query planning, optimisation
I've been trying to optimise our database, but not being a DBA and not having done a whole lot of tuning before I'm struggling a little with "too many options".
I've already run pg_tune and bumped a ...
- 163
3
votes
0
answers
1k
views
MySQL: Improve performance for one row insert into table with unique constraint
I have a table with ~ 40 mil records, with a unique index on the url column, of type varchar(255).
Now the insert speed is about 30/s, is this expected? how could I improve it?
I can't use bulk ...
- 274
3
votes
4
answers
282
views
Optimizing slow query
For a few days now, i'm trying to optimize this query.
But still I haven't found the right solution in how to fix this.
The contents table contains about 20 million records. As the connect table ...
- 31
3
votes
2
answers
255
views
Changing database compatibility from legacy CE to 120
Just seeking an expert/practical advise from DBA point of view where one of our application DB running on SQL 2014 after migration had old DB compatibility level i.e 100.(SQL2008)
From DEV point of ...
- 2,169
2
votes
1
answer
100
views
random_page_cost and memoize plan relation
Postgres 15.2. I have the following simple query:
SELECT mark.last_modified FROM mark
INNER JOIN element ON mark.element_id = element.id
INNER JOIN model ON element.model_id = model.id
WHERE ...
- 135
2
votes
0
answers
109
views
Why is it so fast to create an index on a table?
While optimizing my query in Postgres, I noted that creating an index is always very fast, less than 1 second on a table with millions of rows.
But if there's no index, a query would take a few ...
- 315
2
votes
2
answers
1k
views
MySQL db optimization error on innodb tables
I have a database named 'mysql' which is being used on RHEL 8 server running MySQL 8.0.32.
When trying to optimize the entire database using the mysqlcheck command 'mysqlcheck -o mysql' the below ...
2
votes
1
answer
119
views
MYSQL query slow on sorting
I came across an issue, i have the following query:
SELECT
DISTINCT
p.id,
p.title,
p.description
FROM
(
SELECT
`post_id`,
`s_type`
FROM
`search`
WHERE ...
- 21
2
votes
0
answers
214
views
Why is Postgres 9.6 using a sequential scan when an index would be more efficient
I have a function which is being used on our system. When you run the function direct on the server itself it takes milliseconds to run. However when it is run via the application it takes seconds (...
- 429
2
votes
0
answers
54
views
Separating tables and indexes in Postgresql (possibly in general as well)
Popularly separation of tables and indexes and putting them on different disks is supposed to improve performance.
Does anybody has some evidence on performance, pros and cons of either solution?
Can ...
- 503
2
votes
0
answers
1k
views
How to reduce the cost of a nested loop in a PostgreSQL query
PostgreSQL 13.5, PostGIS 3.2, Windows Server 2012.
My table uniteobservation has 824 605 rows:
CREATE TABLE activite.uniteobservation (
gid serial NOT NULL,
numunobs character varying(20),
typunobs ...
- 207
2
votes
1
answer
108
views
MySQL performance issues with occasional complete freezes
we have a fairly large production DB currently at ~50GB but with constant growth. In the last couple of weeks, we are struggling with performances as some queries are getting very slow even if indexes ...
- 121
2
votes
1
answer
375
views
Optimizing cloud server (php and mysql)
I am running a vBuletin forum as a hobby and I was using a shared hosting service until a couple of days ago. Because the amount of visitors grew and the forum seemed to be slower and slower every day,...
- 31
2
votes
0
answers
550
views
Correlated subquery with common table expressions in SQLite
Yesterday I achieved a 500x performance boost after rewriting an EXISTS with IN. I did some investigation to find out why and finally came to the following example. Basically, we draw 5 lucky numbers ...
- 131
2
votes
2
answers
912
views
Optimising MySQL count() with INNER JOIN (550k rows)
I have the query below, which is currently taking approx 1 - 1.5 seconds to run in a MySQL 5.7 database. It's being called from a web application, and whilst I'll be implementing caching at the ...
- 121
2
votes
0
answers
76
views
MSSQL: Conditional noexpand hint for view that may be indexed or not
As documented in other Q&A on this site, if you create an indexed view you may have to give the noexpand hint to use it as such. Without the hint, cheaper versions of MSSQL will always treat it ...
- 149
2
votes
0
answers
92
views
Improve split large table algorithm in MySQL
I have a very large table ~1TB of history data in MySQL 5.6. I want to split the data into many smaller tables per sites. My current idea is to iterate in chunks of 10'000 records and inside this loop ...
- 163
2
votes
0
answers
64
views
Why can't outer join simplification happen for embedding outer join?
The MySQL documentation here suggests the following:
Sometimes the optimizer succeeds in replacing an embedded outer join operation, but cannot convert the embedding outer join. The following query:...
- 207
2
votes
0
answers
85
views
Querying a High Cardinality Field
I am designing a data model for our orders for our upcoming Cassandra migration. An order has an orderId (arcane UUID field) and an orderNumber (user-friendly number). A getOrder query can be done by ...
- 121
2
votes
1
answer
62
views
How can performance of queries containing keywords like IN, EXISTS, etc. be optimized?
I was interested in some of the general ways one can use to optimize the performance of queries that use keywords like IN and EXISTS (relying on the state of the entire database).
For example, ...
- 123
2
votes
0
answers
427
views
Optimizing a paginated query with various filters and dynamic sorting
Introduction
Imagine a (fairly advanced) ToDo app in which items are catogorized into lists (which can be temporarily deactivated), have custom additional attributes, can be assigned to a user and can ...
- 21
2
votes
0
answers
234
views
View with where clause slower than query with same where clause
This query
with periods (PeriodStart,PeriodEnd,ticker,FundamentalId,dimension) as
(select b.reportdate PeriodStart, MIN(a.reportdate) PeriodEnd,b.ticker, b.FundamentalId,b.dimension
from Base a
...
- 21
2
votes
0
answers
75
views
What's the more efficient way of selecting with multi left joins?
I have 4 tables, the first one is "student", the other 3 tables are different records of the student's activities.
All tables have a id column as the primary key. student.id is varchar(64) (similar ...
- 21
2
votes
0
answers
82
views
Is it possible to replace count distinct inside Correlated sub-queries?
I'm trying to optimize the query bellow. The execution Plan is showing multiple sorting before aggregations (I think this is due to the count distincts in the correlated sub-queries)...
Is it ...
- 21
2
votes
1
answer
66
views
How can I optimize this MYSQL Script For many Records?
I have This script by Helping @lukstorms ,
-- DROP TABLE IF EXISTS test_words;
CREATE TABLE IF NOT EXISTS test_words (
id int unsigned NOT NULL PRIMARY KEY,
words text NOT NULL
);
-- DROP TABLE ...
- 21
2
votes
0
answers
52
views
What is the "Optimize Response" connection string attribute used for?
My apologies if this is not the correct forum to be asking this in.
I am having trouble finding information regarding the "Optimize Response" connection string attribute. The most promising source of ...
- 21
2
votes
0
answers
1k
views
why order by desc and limit query is taking too much time?
Currently, i was trying to make a sql query on my postgresql table, all things was going good unless i sort my query by Price in DESC, after this filter query is taking almost round about 60 sec to ...
- 143
2
votes
0
answers
87
views
Problem query - execution plan
I have a problem with a long running query, I'm not a DBA but it's become my problem. I've been doing a lot of reading over the last few weeks but for the life of me can't figure out why this query is ...
- 21
2
votes
1
answer
473
views
MySQL InnoDB or MySQL NDB Cluster which one to use
We have an application with 8000 users accessing in parallel(max). We are trying to bring in horizontal scaling. I came across MySQL NDB cluster. Should I go with it? Just for auto sharding and other ...
- 121
2
votes
0
answers
591
views
Poor SQL query optimization of Recursive CTE with iTVF, environment specific
I'm developing a recursive CTE which utilizes an inlined TVF. In our dev environment, the recursive CTE executes exactly as expected.
In our production environment, the first iteration of the CTE ...
- 141
2
votes
0
answers
69
views
different ways to optimise union all on a particular staging process
quite often while tuning queries and stored procedures I come across the situation where I have a number of different types of the same object, each type has different characteristics but all of them ...
- 15.8k
2
votes
0
answers
140
views
Why does optimizer estimate 1 row for this plan?
Here's my plan I'm having trouble with: https://www.brentozar.com/pastetheplan/?id=r168tltbe
I'm betting this is something easy I'm not seeing. I have a query that is running very slowly because ...
- 131
2
votes
0
answers
224
views
Procedure runs slower over time; fixed by cache clear or reboot
We are experiencing a strange issue where an application which was fixed by creating missing index requests, updating stale stats, and removing lookups and other scans, was running fine once fixed, ...
- 2,169