Questions tagged [optimization]
In the context of a database, optimisation refers to the process of the query optimiser selecting an efficient physical execution plan.
2,001
questions
0
votes
1
answer
19
views
Copy where clause on both joined tabled, equivalence
I wonder if these two queries are equivalent :
SELECT * FROM t1 JOIN t2 ON t1.id=t2.id WHERE t1.id=42
And:
SELECT * FROM t1 JOIN t2 ON t1.id=t2.id WHERE t1.id=42 AND t2.id=42
Thought, it is a very ...
0
votes
1
answer
42
views
In a "select * where col=x" where x is non-orderable, non-indexed column, does a database give any performance gain over a flat file?
If I am querying a database to select all records that have a VARCHAR() field equal to a query parameter. Is there any speed advantage to doing a linear line-by-line search of a flat-file "...
0
votes
1
answer
42
views
Select by Tuples in PostgreSQL
I have a table user_issues with 3 columns: id, component_id and path and I need to filter it but tuples of component_id and path.
So I used the following query in order to achieve this:
SELECT id
...
- 175
0
votes
0
answers
41
views
Indexing and optimize large query with multiple joins
I have search query with multiple table join as
SELECT
DISTINCT pp.person_profile_id,
pp.middle_name,
pp.prefix,
pp.first_name,
pp.last_name,
pp.suffix,
pp.privacy_type,
...
- 101
3
votes
1
answer
374
views
SQL Server not Performing Join Elimination
Given the StackOverflow2010 database, I have created some foreign keys in order to explore join elimination:
Foreign Key 1 - All comments must have a post
Delete comments without a post
DELETE c
FROM ...
- 2,033
0
votes
0
answers
52
views
Issue with query performance coming from mysql to mariadb
we were in the process to migrate from Mysql to MariaDB, but we've encountered a performance issue with several of our queries.
The same (already simplified) query
SELECT
count(*)
FROM
vorgang,...
- 9
2
votes
1
answer
41
views
Oracle optimizer does not use column group information from multiple tables and misses correlation
I have a problem where the execution of a query is too slow in oracle 12c standard edition.
I figured out, the optimizer estimates wrong cardinalities because it misses the correlation between two ...
- 21
0
votes
1
answer
73
views
MySQL 8.0 no longer updates information_schema.tables after optimizing a table
We have recently been undertaking a purge of data from our databases, which has left some tables with huge amounts of free space (in some cases hunderd of GB's). So I have started a process of going ...
- 2,166
0
votes
2
answers
34
views
Should I create a separate table for non null user_id values in my MySQL database schema for performance?
I have a MySQL database schema for storing uploads, where each configuration can be associated with a user account (user_id) or uploaded as a guest without an associated user account (null user_id). ...
0
votes
1
answer
52
views
Performance issues with time interval check
I'm running the following Postgres query where I join a consumer's usage with the service cost for that interval based on when they used the service from the service table. The query below is a ...
- 3
0
votes
2
answers
78
views
How to simplify this intersect query?
SELECT * from M
left JOIN K on M.id = k.id
left JOIN W on K.str = W.str where str = N'أخبار'
INTERSECT
SELECT * from M
left JOIN K on m.id = k.id
left JOIN W on K.str = W.str where str = N'فلسطين'
...
- 99
0
votes
0
answers
53
views
Mongo DB slow deletes due to indexes rebuild , same with TTL indexes
We are having huge collection which we can not partition ( we need to get the records by last updated time which can not be known ). To decrease the storage we have enabled TTL index based on last ...
3
votes
1
answer
52
views
fetching data from 2 10M+ rows tables
I need to run the following query on my production database, using 2 tables that contain more than 10M rows each :
SELECT rm.parent_id as processed_media_id
FROM render_medias rm
JOIN processed_medias ...
- 131
0
votes
3
answers
82
views
What index can be created to optimise this query?
I've got the below SQL query that runs extremely slowly. As for this query, this is due to the "ORDER BY" statement, since Postgres is scanning the changes table by "counter" which ...
- 191
-1
votes
1
answer
83
views
How do you eliminate extra transaction log files?
I started a new job taking over for a guy that really did not know what he was doing.
He created multiple transaction log files for most of the databases on the server.
Apparently, he did not know how ...
0
votes
3
answers
115
views
How to best optimize computer for intensive Mysql queries?
I have a series of tasks I run in MYSQL 8 every day. Actually these tasks are run from a vb.net application that interfaces with MYSQL using 8 connections at a time. These tasks are a series of ...
- 23
0
votes
1
answer
55
views
Why does Postgres give different EXPLAIN ANALYZE results when using now() vs current_timestamp?
At my job we've recently had to optimize a query and kind of stumbled into the answer to make the query faster but can't explain why it worked and it's bothering me.
The situation is something like ...
- 213
0
votes
0
answers
30
views
Performance difference in SQL queries using JOINs with function
I have two SQL queries that are structurally the same, but have a significant difference in execution time.
The first query takes around 500ms to execute, while the second one takes only 50ms.
Both ...
- 33
2
votes
1
answer
81
views
How to optimize following subnet that is decomposed into multiple sub-one?
I have a table which like like that:
Id
IpRange
Mask
Owner
1
127.0.0.0
24
1
2
127.0.1.0
24
1
3
127.0.2.0
24
1
4
127.0.3.0
24
3
I'm checking if an IP is in the range thanks to this :
(inet_aton('...
- 181
0
votes
1
answer
90
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 ...
0
votes
1
answer
65
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
144
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.
...
- 645
9
votes
1
answer
191
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 ...
- 201
1
vote
1
answer
89
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
38
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 ...
- 101
0
votes
3
answers
55
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 ...
- 48
0
votes
0
answers
24
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
50
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 ...
- 13
0
votes
1
answer
135
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 ...
- 141
1
vote
2
answers
48
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
16
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
234
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
416
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, ...
- 141
0
votes
1
answer
130
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 ...
- 123
1
vote
2
answers
60
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 ...
- 111
0
votes
1
answer
45
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 ...
- 141
0
votes
1
answer
109
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 (...
- 101
1
vote
1
answer
103
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 ...
- 111
2
votes
1
answer
505
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 ...
- 123
0
votes
0
answers
33
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, ...
- 1
2
votes
2
answers
686
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....
- 123
0
votes
0
answers
55
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
...
- 101
0
votes
2
answers
111
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 ...
- 27
1
vote
1
answer
270
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 ...
- 27
0
votes
0
answers
28
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 ...
- 27
1
vote
2
answers
81
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 ...
- 111
1
vote
1
answer
53
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 ...
- 111
1
vote
1
answer
76
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 ...
- 23
0
votes
1
answer
274
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,...
- 349
1
vote
1
answer
193
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 ...
- 13