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,987
questions
0
votes
0
answers
3
views
How to find IP subnet that is decomposed into multiple sub-one?
I have a table which like like that:
Id
IpRange
Mask
1
127.0.0.0
24
2
127.0.1.0
24
3
127.0.2.0
24
I'm checking if an IP is in the range thanks to this :
(inet_aton('here is the IP') & ((POWER(...
0
votes
1
answer
56
views
Slow query when using OR clause
I have 4 tables and it takes quite a long time to query with OR clause (AND clause works fine).
News articles
Rows: ~7000
Schema:
id | integer
publication_date | timestamp with time ...
-1
votes
0
answers
27
views
How to force MariaDB use all cores? [duplicate]
My /etc/my.cnf has the following lines, as recommended by this answer:
[server]
innodb-thread-concurrency = 0
innodb-read-io-threads = 64
innodb-write-io-threads = 64
But when I do a query that takes ...
0
votes
1
answer
51
views
Data servers: Do query optimisers re-write queries removing redundant columns during the plan creation?
Although I'm using SQL Server, as this is a question of whether an optimiser will re-write a query to remove redundant columns, I'm pitching this at all data servers: RDBMS, NoSQL, MPP, anything ...
0
votes
1
answer
54
views
How can functions that make heavy use of table variables be optimised?
Suppose that you have a multi-statement table-valued function that makes heavy use of table variables. What methods exist for optimising them? I only know of the following and they all disappoint me.
...
7
votes
0
answers
136
views
SQL Server: how Row Goal affects the same index operation?
I am tuning some SQL performance in the SQL Server 2019, and I ran into issues with one query that involves the issue with the row goals. The simplified query is:
select TOP 1 ISNULL(RUNID,-1)
from ...
1
vote
1
answer
82
views
How to improve performance for this query ? Any Suggestions
Query:
with firstData as (
select r.display_name as counter,
r.createdat,
r.status,
r.id as runid
from run r
inner join customer c on c.id = r.badmasterid
...
0
votes
2
answers
33
views
Optimizing Reads on versioned table with multiple ANDed "IN" clauses
I am trying to find an optimized way to query for some data given some very particular tables. Frankly, I think some of the optimization might involve a table refactor, but I am trying to stave that ...
0
votes
3
answers
41
views
PostgreSQL enforcing order dependent constraints within a query
Is there a best practice for enforcing the order in which search conditions are applied? We have seen a few queries where the optimizer has reordered the operations and passed rows through a search ...
0
votes
0
answers
19
views
Local SQL to Managed Cloud SQL via ADF. Copy Data throughput fell off a cliff
I have an on-site mini PC acting as a data extraction and replication SQL server that copies data nightly from the ERP production database that's running some legacy linux architecture. The copying is ...
1
vote
2
answers
42
views
Optimized conception for score table
I have a simple question yet I couldn't find a direct answer/explanation. Sorry if duplicate
I want to build a score table for a small quiz game where users get questions and they can either be right ...
0
votes
1
answer
36
views
Strategy for Postgres updates with very high write load, but low consistency requirements
I need to collect statistics on profile thumbnail views for a social media app, backed by Postgres. Every time the thumbnail of a user appears anywhere on a page (in a list of profiles, or next to ...
1
vote
2
answers
40
views
Table partitioning for data pruning purposes
We are using MySQL database. The total space it can accommodate is 250 GB. It is currently at 225 GB. With this, there had been this initiative within our team to create a daily cron job for daily ...
0
votes
0
answers
15
views
Distributions when only one physical node
Working on Azure Synapse, we have for now around 30 tables on a dev environment.
I want to optimize the tables before replicating them in qal and prod env.
As far as I understand, we only have one ...
1
vote
3
answers
104
views
The mariadbd process is at over 140% CPU utilisation despite a high innodb_buffer_pool_size value
I have a Plesk Ubuntu 22.04 server with 120GB RAM and mariadb 10.6.16 that I use to run the database for a single website. The database alone is about 8GB but despite all the optimisations I've made ...
1
vote
2
answers
40
views
How to optimally do Postgres `IN (...set...)` query with a large set of values
I have a table with millions of rows, and I want to find all rows that have any one of a supplied list of a few thousand values in a specific column. Bascially I want to run an IN(...set...) query, ...
0
votes
1
answer
41
views
Sharding a Materialized View
I am a Postgres 15.3 AWS RDS instance and have a database that centers around one table, called posts that has 20 other relations to build the full object. The full query is about a second altogether ...
1
vote
2
answers
56
views
MySQL optimize query against table containing 13M records
I recently started working with a legacy Rails application. Trying to go through with some of the queries used to get analytics related data. Some table contains around 13M records. Running count ...
0
votes
1
answer
29
views
Evaluation order (commutativity/associativity) of ANDed terms in Postgres WHERE clause
Is Postgres' query planner able to rearrange terms in a WHERE clause according to the rules of commutativity and associativity?
For example, do the following WHERE clauses all result in a query plan ...
0
votes
1
answer
61
views
Optimized count for large table using triggers, views, or external cache
I have a public API method that calls a Postgres (14) database and returns a paginated list of rows belonging to a user along with a total count and page index. The count is very costly to perform (...
1
vote
1
answer
57
views
Optimizing type alignment for table with many of variable-length columns in Postgres
I have the following DDL for a Postgres table that was generated by JPA:
postgres=# \d+ mytable;
Table "public.mytable"
Column ...
2
votes
1
answer
103
views
optimize SELECT MAX() with GROUP BY in Postgres?
I'm trying to speed up the following query in postgres:
select MAX(msg."timestamp") AS latestDate, msg.channel_id from message msg group by msg.channel_id
the explain is as such:
Finalize ...
0
votes
0
answers
30
views
Speedup wordcloud select with group by in mysql
I am currently working on a project for my studies where a wordcloud is created for the NYT. Therefore I am scraping all the articles from the NYT from their API, and then store dates of the article, ...
2
votes
2
answers
151
views
Improve query performance of filtered left outer join of big tables
I am trying to optimize a query that joins two large tables (40MM+ rows) in PostgreSQL 15.4.
SELECT files.id, ARRAY_AGG(b.status)
FROM files
LEFT OUTER JOIN processing_tasks b
ON (files.id = b....
0
votes
0
answers
28
views
Duplicating (but not using) a materialized CTE makes the query perform as it should
I have a large query with many CTE's and joins, and once I changed the amount of data in the database (from 20k rows to 10k), it started performing horribly: https://explain.depesz.com/s/TSu3
...
0
votes
2
answers
81
views
formating date colums causing performance issues
I have a query(written by my previous co-worker) with date columns returned as output but formatted, which s causing performance degradation (for below query returning 10k records, performance ...
1
vote
1
answer
118
views
Using table valued function as a table in sql server join
I have a scenario where SQL server using table valued function with joins to many other tables. The function returns many columns, which are not always desirable output for every report, causing lots ...
0
votes
0
answers
24
views
resume search with full text index
I have a database structure where resume stored in document column in WHDocument table in database for each person. During skilled person search in application the persons skill is also searched for ...
1
vote
2
answers
59
views
Is there a "correct" way for an optimizer to process a subquery if the outer query has additional where filters?
This topic came up for me as the result of investigating an error in Redshift. We have a table containing a column of numbers represented as text, but for rows where no value is present a single ...
1
vote
1
answer
48
views
Optimizing the SQLite database as source data for R scripts
I have an application written in R Shiny that supports a large number of csv's (and 3 new ones are added every day). These files have a structure that is quite unpleasant to handle, so I decided to ...
1
vote
1
answer
57
views
MySQL - Slow Performing Subquery when DISTINCT Is Used
I have this query that runs very slowly when DISTINCT is used;
SELECT COUNT(*) FROM (SELECT DISTINCT query_text FROM search_query WHERE (store_id IN (1)) AND (num_results > 0) ORDER BY popularity ...
0
votes
1
answer
103
views
SQL Server - Update Statistics FULL scan vs Auto Update [duplicate]
Something I have been trying to figure out..
I take a table with 2 billion + rows and I run a manual stats update with FULLSCAN
Sometime in the future, an automatic update stats runs against the table,...
1
vote
1
answer
84
views
Wrong execution plan in MySQL 8.0 when using JOIN in WHERE NOT IN clause
After a migration from MySQL 5.7 to MySQL 8.0.34, we have a very strange behavior with a query when semijoin is on in the optimizer_switch.
1. The problematic query:
SELECT COUNT(s0_.id)
FROM ...
1
vote
2
answers
646
views
How to optimize a query on a large table, besides using index?
I need suggestions/ideas of optimizing the following query:
SELECT COUNT(*)
FROM (SELECT 1
FROM f
WHERE parent_sha256 = $1
AND parent_sha256 <> sha256
LIMIT 1) AS ...
1
vote
1
answer
133
views
SQL 2019 - BatchModeOnRowStoreUsed - Legacy Cardinality - IQP
Have recently migrated from SQL 2016 where the DB's compatibility level was all set to 2008 to SQL 2019 where the compatibility level for all DB's are now 150.
Upon running workload for a while, I ...
0
votes
0
answers
39
views
Optimizing SELECT COUNT(DISTINCT) on a table increasing daily
Let's say we have a table Daily_users which has the columns student_id, school_id, grade, timestamp. We collect usage data of students daily and so the table grows daily (note that there could be ...
0
votes
2
answers
83
views
How to choose index to optimize this MySQL query?
SELECT * FROM ehd_historical_data
WHERE exchange = 'NYSE ARCA' AND symbol = 'ZROZ' AND blacklisted = 0
ORDER BY high DESC LIMIT 1;
SELECT * FROM ehd_historical_data
WHERE exchange = 'NYSE ARCA' ...
1
vote
2
answers
116
views
Why can SQL Server not perform a TOP N SORT between an Index Seek and the Key Lookup?
I am looking into a minor performance issue, where an optimizer tool is basically saying, "hey just go ahead and include all the columns on this table in this index" which is a horrible ...
1
vote
1
answer
41
views
Finding the latest status
I have 2 tables events and event_status.
The event_status table is the history table, and latest being the current one. The events and event_status have a one-to-many mapping.
I need a optimized query ...
0
votes
1
answer
59
views
How to optimize a query in postgres?
I have three tables: company, employments and entries. A company has many employments and a single employment has many entries.
The entries table has the most items in the entire database (currently ...
0
votes
0
answers
86
views
How can I optimize the view in MariaDB for Order By?
I have a list of products. Each has many tags. Those tags have normal tables, but they also have OLAP tables that present CSV for each product.
For example, a chair can have 'furniture', 'wood', and '...
0
votes
0
answers
27
views
MongoDB indexing for dynamic query
I'm trying to build a query optimizer for survey response data, all questions are stored in a collection and have some fields are number_of_selection, option_marked etc.
Now I'm trying to build a ...
0
votes
0
answers
36
views
Faster running for this query sql server
i used database engine tunning advisor for creating some index and statistics but still run slow
SELECT
C.code_commande AS 'Order',
C.code_client AS 'Cust.',
...
3
votes
1
answer
348
views
PostgreSQL Query Planner overestimating Row Count leads to slow Hash Join
I have a table with two columns and about 10M rows. About half the records are NULL in the first field, and about half the records are NULL in the second field, although there are a few hundred ...
0
votes
0
answers
36
views
PostgreSQL Optimizer does not find best solution when specifying subset of keys in query
PostgreSQL (Version 15) does not find a (in my opinion) obvious optimization for the query plan, is there a way to help the optimizer with this problem?
I have a specific kind of query where I have to ...
5
votes
1
answer
182
views
Partitioned view aggregation query not optimized
Given the following two tables:
CREATE TABLE SalesLedger (
Id int PRIMARY KEY IDENTITY,
Date date NOT NULL,
Total decimal(38,18),
INDEX IX (Date, Total)
);
CREATE TABLE Purchases (
Id int ...
0
votes
1
answer
92
views
Help with SQL Query: Is there a cost-efficient way to join multiple tables to one main table?
I have a main table set up like this:
id
table_to_join
table_item_id
1
products
123
2
products
577
3
products
344
4
products
1230
5
images
14
6
images
42
7
video
555
8
products
400
9
video
9
...
0
votes
1
answer
234
views
How to optimise a query with inner join and order by
I'm struggling with optimising my query. I've tried to solve the issue with various indexes by none of them seems to be helpful.
There are 3 tables: transfers(id, amount, type, timestamp, file_id), ...
1
vote
1
answer
33
views
Slower MySQL queries involving both wp and custom table
Below are less efficient MySQL queries involving wp posts, taxonomies, and a custom movie table search. The custom movies table contains around 30k rows, and wp posts have been generated with the ...
-2
votes
1
answer
29
views
Database for video from the internet [closed]
I am an direction student from India working in advertising. For making treatment note and pitch deck I require a lot of photos for referencing from different videos from the internet readily ...