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
0 answers
15 views

Is a foreign key (FK) index "slower" when FK column is nullable (allows NULL)? (Postgres)

For a long time, I have held a belief that "nullable foreign key" columns are "slower" because the FK index will be slower. I read it somewhere once and it stayed with me. I cannot ...
user avatar
0 votes
0 answers
11 views

Postgres 12 Move and Delete Data using (CTEs) WITH statements causing parsing issues - best approach?

Am not sure if this is the best method of moving/deleting data. I have 2 tables play and game, information from the play table is referenced from the game table. I am trying to find all games that are ...
user avatar
  • 173
-2 votes
0 answers
14 views

MySQL select script is slow

Good day! I have a problem in MySQL and PHP. I have a script with only 16 data in the table. and I have a simple script. Sometimes querying is slow and it takes to 2minutes to display. I have 128 RAM ...
user avatar
  • 1
0 votes
1 answer
59 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
20 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
33 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
36 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
  • 131
1 vote
1 answer
37 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
832 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
48 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
8 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
72 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. When I arrived I found other ETLs that also ...
user avatar
0 votes
1 answer
33 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
31 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
56 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
  • 407
1 vote
1 answer
56 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
85 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
109 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
45 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
25 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
68 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
36 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
71 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
38 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
83 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
43 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
  • 173
2 votes
2 answers
154 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
30 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
11 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
181 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
  • 163
0 votes
1 answer
128 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
182 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
84 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
36 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
57 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
  • 171
1 vote
1 answer
79 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
77 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
  • 173
0 votes
1 answer
58 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
87 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
138 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
80 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
176 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
68 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
0 votes
0 answers
45 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 ...
user avatar
1 vote
1 answer
76 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 ...
user avatar
  • 11
0 votes
1 answer
214 views

MySQL not using index as expected with a WHERE IN clause

I have a table called mytable and I'm trying to optimize a query on it. I generated the index ind5 on it, and it has the nullable int column col_1 in first place. This query has a WHERE IN clause, ...
user avatar
  • 130
0 votes
0 answers
151 views

Fastest way to insert in-memory JSON data into Postgres, other than json_populate_record

We ingest a lot of data, and one of the most time-consuming tasks is inserting data that NodeJS has in JSON format into the database. Currently we're running a query like this, with the giant JSON ...
user avatar
  • 101
0 votes
1 answer
104 views

SQL query working with 8.0 but not working with 5.7 [closed]

I have a mysql table like this CREATE TABLE `users_search_activity` ( `ID` bigint(20) UNSIGNED NOT NULL, `user_id` int(11) NOT NULL, `country_id` int(11) NOT NULL, `search_keywords` text ...
user avatar
  • 661
0 votes
1 answer
54 views

MySQL memory usage overflow cause VM to reboot

I have 5 mysql servers in master-master group replication setup. (MySQL version 8.0.26) 2 nodes are having 1vCore and 2GB RAM and 20GB HDD configurations, and rest 3 nodes are having about 32GB RAM ...
user avatar
4 votes
1 answer
101 views

Optimize query matching first N items of an array

I'm working with a Postgres DB full of chess game data right now, where each game is a row in a 'records' table. The players' moves and the (optional) computer evaluations of those moves each have ...
user avatar

1
2 3 4 5
38