Questions tagged [optimization]

In the context of a database, optimisation refers to the process of the query optimiser selecting an efficient physical execution plan.

Filter by
Sorted by
Tagged with
-1 votes
1 answer
12 views

Optimize 5 million records SQL query (use join or not?!)

I have a table for products that contains almost 5 million records. I have a column for the product category (product_category). That it is for now of type INT(11) and it is an index and refers to ...
user avatar
0 votes
0 answers
29 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'...
user avatar
0 votes
1 answer
36 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 ...
user avatar
0 votes
1 answer
39 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 ...
user avatar
  • 101
4 votes
2 answers
104 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 ...
user avatar
2 votes
1 answer
42 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 ...
user avatar
  • 21
3 votes
0 answers
48 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....
user avatar
  • 1,072
0 votes
2 answers
36 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....
user avatar
1 vote
1 answer
49 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 ...
user avatar
  • 13
0 votes
1 answer
38 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://...
user avatar
0 votes
1 answer
123 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 ...
user avatar
  • 115
0 votes
1 answer
37 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 ...
user avatar
-1 votes
1 answer
38 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 `...
user avatar
  • 23
1 vote
2 answers
42 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 ...
user avatar
  • 133
1 vote
1 answer
43 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 -----------------+---------------+-----------+----------+----...
user avatar
  • 95
6 votes
1 answer
902 views

Do datatypes of columns affect query performance?

Let's suppose I have this table: Table "public.orders" Column | Type | Collation | Nullable | Default -----------------+---------------+-----------+----------+----...
user avatar
  • 95
0 votes
1 answer
56 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 ...
user avatar
  • 95
0 votes
0 answers
24 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 ...
user avatar
  • 1
0 votes
1 answer
78 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 ...
user avatar
0 votes
1 answer
39 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 ...
user avatar
1 vote
1 answer
34 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). ...
user avatar
0 votes
2 answers
59 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 ...
user avatar
  • 417
1 vote
1 answer
59 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 ...
user avatar
  • 115
1 vote
1 answer
115 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 ...
user avatar
0 votes
5 answers
238 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....
user avatar
0 votes
1 answer
84 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*...
user avatar
  • 95
0 votes
1 answer
29 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 ...
user avatar
1 vote
1 answer
103 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 ...
user avatar
  • 31
0 votes
2 answers
42 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 | ...
user avatar
1 vote
1 answer
104 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 ...
user avatar
2 votes
1 answer
53 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,...
user avatar
  • 21
3 votes
2 answers
148 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, ...
user avatar
2 votes
0 answers
52 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 (...
user avatar
  • 185
2 votes
2 answers
307 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 ...
user avatar
  • 121
1 vote
0 answers
33 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 ...
user avatar
0 votes
0 answers
13 views

Optimising Recursive SQL Commands

I am using BigQuery. I have a script that generates row policies for a specific table. The script takes 5-10 mins to run and generate the policies in a Pandas data frame. The final loop where each ...
user avatar
3 votes
1 answer
221 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 ...
user avatar
  • 195
0 votes
1 answer
167 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 ...
user avatar
  • 5
2 votes
0 answers
320 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 ...
user avatar
  • 167
0 votes
2 answers
94 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 ...
user avatar
0 votes
2 answers
40 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 ...
user avatar
  • 165
0 votes
1 answer
63 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 ...
user avatar
  • 181
1 vote
1 answer
85 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; ...
user avatar
  • 111
-1 votes
1 answer
91 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 ( ...
user avatar
  • 185
0 votes
1 answer
81 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 ...
user avatar
1 vote
2 answers
106 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 ...
user avatar
0 votes
2 answers
174 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 ...
user avatar
1 vote
0 answers
90 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 ...
user avatar
0 votes
1 answer
318 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; ...
user avatar
2 votes
1 answer
103 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 ...
user avatar
  • 23

1
2 3 4 5
39