Questions tagged [optimization]
In the context of a database, optimisation refers to the process of the query optimiser selecting an efficient physical execution plan.
1,964
questions
0
votes
1
answer
57
views
Sql Server optimizer trees: CONVERTED and TREE_COPIED_OUT
Using the trace flags 8605, 8606, and 8607 in a Sql Server query, we can have a look at the logical trees that are built. Trying to associate each tree with the optimization phases of the First Step I'...
- 63
0
votes
1
answer
1k
views
Performance considerations when adding a new column to a big table in Sql Server
I have a table with 100 million rows and 70 columns. I'm planning on adding a new DateTime column to this table but I'm concerned that it might affect its performance. Would it be better to create a ...
- 437
0
votes
1
answer
183
views
MySQL - Optimize select with join to a large table
I have done some digging and found exactly no help at all for questions similar to this one. Basically I have two tables where one stores user posts and the other holds the moderation of the records ...
- 101
4
votes
2
answers
971
views
Optimise query for fetching data across multiple days in a partitioned table
I'm trying to optimise a query to retrieve records across multiple days on a partitioned table (say: entries). The table is partitioned on a timestamp column (let's say: created_at) and for each day a ...
- 141
2
votes
1
answer
511
views
Imporve MySQL InnoDB Full-text Search Performance
1.Background:
DB: MySQL 8.0, InnoDB engine;
Table size: About 2M rows of 2G data; FULL_TEXT index column: sentence (TEXT data type) https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html
Old ...
- 21
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,192
0
votes
1
answer
50
views
Query optimization [Oracle DBMS]
I have a query that always must return a single row and its execution time is about 5s, so I need to optimize it:
SELECT * FROM RESPONSE res
INNER JOIN REQUEST req ON res.ID = req.ID
WHERE res....
- 1
1
vote
1
answer
141
views
Is a correlated function in the FROM clause executed for every row? [closed]
I have a heavy function, let's call it fcalc(x,y) -> my_z. I need the result my_z to both be a filter (too low and the row is discarded) and in the result set (so my client can see it). I write the ...
- 13
0
votes
1
answer
868
views
Postgres query with large IN, and joining on a temporary table doesn't seem to work
EDIT: The query plans in the question body come from EXPLAIN, but as @jjanes suggested, EXPLAIN (ANALYZE, BUFFERS) may be more useful. Since the output is very large I have uploaded them here: https://...
- 103
0
votes
1
answer
1k
views
Optimize filter on SQL query on PostgreSQL
I have a query with a high cost on filtering the results and I am thinking I should be adding an index to optimize the plan, but the indexes I tried till now had no impact. Can I optimize the query by ...
- 115
0
votes
1
answer
226
views
How to daily download and merge a csv file and update on Google sheets?
Disclaimer: I’m not sure if this question goes on here. I’ve trying to solve this issue for quite a while and I don’t think there is another way other than using a python script.
I need to solve this ...
- 101
-1
votes
1
answer
1k
views
Optimization : JOIN or CTE [closed]
Suppose that I have these tables,and example data (the real table I am working on has more columns)
table `users`
id
name
zip
1
John
201
table `region`
zip
city
state
country
22
a
b
c
table `...
- 23
1
vote
2
answers
130
views
Could MySQL perform better on a 10GB network storage with raid vs local HDD?
Compared to using a HDD on local computer and do many MySQL read / writes on it, could it have more queries per second if I install 10GBe network cards and I store the MySQL data on a raid network ...
- 195
1
vote
1
answer
104
views
Why is the optimizer not using the clustering index on my table?
I have this table
Table "public.lineitem"
Column | Type | Collation | Nullable | Default
-----------------+---------------+-----------+----------+----...
- 235
8
votes
1
answer
2k
views
Do datatypes of columns affect query performance?
Let's suppose I have this table:
Table "public.orders"
Column | Type | Collation | Nullable | Default
-----------------+---------------+-----------+----------+----...
- 235
0
votes
1
answer
197
views
How to speed up subquery
Let's suppose we have:
select
o_orderpriority,
count(*) as order_count
from
orders
where
o_orderdate>='01/07/1993'and o_orderdate<'01/10/1993'
and
exists
(select
*
from
...
- 235
1
vote
0
answers
255
views
How to optimise a Redshift table with a SUPER data type column?
We currently have a small table in Redshift with 3 columns
col1 (id): varchar(36)
col2: varchar(36)
col3: SUPER
Where the SUPER column contains a dictionary which can vary in length.
The total size ...
- 11
0
votes
1
answer
390
views
Index Optimization for tables that are truncated and charged everyday
I'm a recently recruited Data Engineer, I have made a bunch of ETL pipelines planned to run daily, the destination tables are truncated and charged again. The DBMS is on-prem SQL Server.
When I ...
1
vote
1
answer
217
views
Autovacuum does not clear the database
Autovacuum does not clear the database.
Databases middle-db
Postgres 10.18, AWS RDS(vCPU 2, RAM 8Gb, SSD(gp2) 1100Gib)
Table "spree_datafeed_products"
relid | 16556
schemaname ...
1
vote
1
answer
70
views
mysql optimization for intensive schema-altering queries (DDL)
In the context of CI would like to massively parallelise the running of tests. The code under testing require a database to be present, so MySQL is used (as the application goes with that right now).
...
- 303
0
votes
2
answers
109
views
UDF and built-in function behavior
Take the following queries:
DECLARE @X VARCHAR(200) = '1,2,3,4'
SELECT
*,
dbo.aUserDefinedScalarFunction(4) AS ScalarValue
FROM
MyTable T
INNER JOIN dbo.aUserDefineTableFunction(@X) A ...
- 495
1
vote
1
answer
120
views
Optimizing simple query in PostgreSQL (two table join and sorting)
I have a simple query that should return data about booking, but it misbehaves and sometimes does not return data within 1 minute.
So I have main booking table:
table booking_data
- bid bigint primary ...
- 115
1
vote
1
answer
576
views
Simple query with a single join very slow
I have this very slow, simple query that joins a large table (~180M rows) with a smaller table (~60k rows) with a foreign key, filtering an indexed column on the smaller table, ordering by the primary ...
2
votes
3
answers
6k
views
MySQL's maximum memory usage is dangerously high
I'm trying to understand what is not working in my configuration.
When I run the
[root@server ~]# ./mysqltuner.pl
>> MySQLTuner 1.9.8
* Jean-Marie Renouard <jmrenouard@gmail....
- 21
0
votes
1
answer
647
views
Optimize GROUP BY, ORDER BY and many SUM and AVG operation query
I have this query, from TPCH-H benchmark:
explain analyze select
l_returnflag,
l_linestatus,
sum(l_quantity) as sum_qty,
sum(l_extendedprice) as sum_base_price,
sum(l_extendedprice*...
- 235
0
votes
1
answer
38
views
Inhibiting results to pane in SSMS (TSQL language) via script
I found that one could use a menu to "discard" results in SSMS (TSQL language). Please see: Original Question about inhibiting results and its answer.
However, I was wondering if there is a ...
- 147
2
votes
1
answer
722
views
Optimize performance of nested GROUP BY
I have a query on the table random_numbers with a GROUP BY nested inside another GROUP BY:
SELECT
a,
min(x_max) AS x_minmax
FROM (
SELECT
a,
b,
max(x) AS x_max
...
- 51
0
votes
2
answers
97
views
Fastest query to process data in small batches without repetition
I have java app that is using MySQL in the backend. I have the following table:
A = int, B = varchar, C = timestamp
A | B | C
1 | 100 | 2022-03-01 12:00:00
2 | 200 | 2022-03-01 12:00:01
3 | 100 | ...
- 101
1
vote
1
answer
1k
views
Optimizing expensive GROUP BY / ORDER BY query on 690,000 row table
I am writing a query to calculate hiscore rankings for a game. It performs a GROUP BY to aggregate hiscore statistics for individual player stats that are stored across 23 different rows (separated by ...
- 13
3
votes
1
answer
305
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
2
answers
826
views
Should JSON columns be separate from a wide table in MySQL?
JSON is stored similar to LONGTEXT datatypes. The MySQL docs advise for TEXT datatypes:
If a table contains string columns such as name and address, but many queries do not retrieve those columns, ...
2
votes
0
answers
212
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
2
answers
2k
views
Postgres : Indexing switches to Parallel Seq Scan from index scan when queried for date range exceeding 150 days
I have two tables - exam_data and series_data, with 300M and 900M records each. I have a query which joins the two tables. If I pull the records for the last 90 days, then the query returns within a ...
- 121
2
votes
0
answers
53
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
3
votes
1
answer
712
views
Making an index a unique index in very large MySQL table within one transaction - is the following approach safe?
For the purpose of optimizing SELECT statements I am trying to make an index UNIQUE with the following SQL statement in MySQL:
ALTER TABLE credentials DROP INDEX special_credential_id, ADD UNIQUE KEY ...
- 215
1
vote
1
answer
786
views
In Postgresql, can I create a column of a one-dimension array type with foreign key constraint? If not, how to simulate it?
In essence I have to design todo table, and an item table. I need to preserve item order, as well as allowing client to rearrange the order. If postgres allow an array of foreign key that would be ...
- 25
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
0
votes
2
answers
225
views
How to optimize a big table for backup
I have a table that is not too much big but still, it takes 30GB of storage.
When I try to make a backup with mysqldump it takes a lot of time and resources and I want to optimize it but I don't know ...
- 103
0
votes
2
answers
56
views
MySQL - Planning Cost Higher Than Number of Blocks Occupied in Disk
There is a table dept and it has 100 rows, all nicely tucked in 1 block, as seen in the mysql.innodb_index_stats table.
table_name
index_name
stat_name
stat_value
sample_size
stat_description
dept
...
- 207
0
votes
1
answer
332
views
Difference between a query based on CTE and a simple query
i'm doing a select starting from a table named events that is joined with device measurements using a date between function (this time between is a sort of "mobile windowing") in order to ...
- 181
1
vote
1
answer
112
views
Efficient short query for light tracking system
Considering this schema for a time series fights database named OpenSky:
SET search_path = public;
CREATE EXTENSION IF NOT EXISTS POSTGIS;
DROP TABLE IF EXISTS "FlightsSummary" CASCADE;
...
- 111
0
votes
1
answer
218
views
Should I alter an Index to include a filter in Postgresql 9.6
I have a query which I am looking to improve on with potentially a new index.
SELECT COUNT(price_id)
FROM price.price
WHERE (
(
(userid = '3081217')
AND (
...
- 429
0
votes
1
answer
537
views
Concatenate duplicates within table column [duplicate]
I have a query that is returning data similar to the following:
po_line.line_no (PO Line #)
oe_line_po.order_number (Sales order #)
1
A
1
B
1
C
2
D
3
E
3
F
4
null
Some of our po_line.line_no ...
- 11
1
vote
2
answers
350
views
How are the scalar functions executed by the optimizer
I have read online that scalar functions can affect the performance since the optimizer does not have access to the contents of the scalar function. Since the function is executed for each row, does ...
- 53
0
votes
2
answers
674
views
MySQL multiple subqueries - slow ordering
Any idea how to optimise this query?
It runs very slow when using the ORDER BY (~10 seconds) clause. If I remove the order_by clause, then it executes for milliseconds.
Problem is, that I need to been ...
1
vote
0
answers
156
views
PostgreSQL: Taking into account correlation between null / not null values of columns during planing
Question:
Is PostgreSQL able to take into account correlation between null value of one column and not null values of other columns during planing? Does "Cross Columns Stats" feature allow ...
- 188
2
votes
2
answers
1k
views
Nested loop on primary key
CREATE TABLE `request` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`created_by` int(11) unsigned DEFAULT NULL,
`content` text,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
...
2
votes
1
answer
437
views
Filtering a large number of rows in Cassandra
Let's assume we have a lot of potentially heavy rows (e.g. 500k) in a table that we want to filter by primary keys and send over the internet to a processing engine.Is it reasonable to use the IN ...
- 23
0
votes
0
answers
87
views
Is there any alternative to Sage Evolution's way to backup & restore databases
I am currently working on a handful of web apps that work in coherence with our Sage 200 (Evolution) database.
However, one problem that we have noticed at the moment is the ability to backup and ...
- 101
1
vote
1
answer
131
views
Best solution for performance - to extract 400 over fields with varchar (300) for one single BI Data set
I have this requirement to extract about 450 fields with most of it's fields length with varchar(300) from multiple tables for one single BI report.
For better performance, I tried to create one ...
- 11