Skip to main content

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
2 votes
1 answer
28 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 ...
egen's user avatar
  • 21
0 votes
1 answer
24 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 ...
IGGt's user avatar
  • 2,146
0 votes
2 answers
32 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). ...
user3075373's user avatar
0 votes
1 answer
41 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 ...
orchid's user avatar
  • 3
0 votes
2 answers
66 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'فلسطين' ...
simpatico's user avatar
0 votes
0 answers
24 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 ...
Yulian Oifa's user avatar
3 votes
1 answer
47 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 ...
VaN's user avatar
  • 131
0 votes
3 answers
81 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 ...
laurent's user avatar
  • 191
-1 votes
1 answer
58 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 ...
David Eaton's user avatar
0 votes
3 answers
92 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 ...
Luke Krell's user avatar
0 votes
1 answer
36 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 ...
Becuzz's user avatar
  • 213
0 votes
0 answers
25 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 ...
Timon's user avatar
  • 33
1 vote
0 answers
26 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(...
Elikill58's user avatar
  • 221
0 votes
1 answer
80 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 ...
marylandmusclemachine's user avatar
0 votes
1 answer
55 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 ...
Steve Martin's user avatar
0 votes
1 answer
119 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. ...
J. Mini's user avatar
  • 509
8 votes
0 answers
143 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 ...
Jan Drozen's user avatar
1 vote
1 answer
87 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 ...
ankush chauhan's user avatar
0 votes
2 answers
36 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 ...
Mrpebbles's user avatar
  • 101
0 votes
3 answers
46 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 ...
Justin Lowen's user avatar
0 votes
0 answers
21 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 ...
funkyman50's user avatar
1 vote
2 answers
47 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 ...
1000k's user avatar
  • 13
0 votes
1 answer
77 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 ...
Luke Hutchison's user avatar
1 vote
2 answers
42 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 ...
Xmus Jackson Flaxon Waxon's user avatar
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 ...
Gregoire_M's user avatar
1 vote
3 answers
134 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 ...
Nelson Nokimi's user avatar
1 vote
2 answers
136 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, ...
Luke Hutchison's user avatar
0 votes
1 answer
83 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 ...
Frank Conry's user avatar
1 vote
2 answers
59 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 ...
Aparichith's user avatar
0 votes
1 answer
35 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 ...
Luke Hutchison's user avatar
0 votes
1 answer
79 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 (...
JackMahoney's user avatar
1 vote
1 answer
77 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 ...
user991710's user avatar
2 votes
1 answer
231 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 ...
tuskiomi's user avatar
  • 123
0 votes
0 answers
31 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, ...
Ynfiniti's user avatar
2 votes
2 answers
366 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....
Jeff G's user avatar
  • 123
0 votes
0 answers
35 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 ...
localhost's user avatar
  • 101
0 votes
2 answers
87 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 ...
Madhusudan's user avatar
1 vote
1 answer
192 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 ...
Madhusudan's user avatar
0 votes
0 answers
25 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 ...
Madhusudan's user avatar
1 vote
2 answers
61 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 ...
MattB's user avatar
  • 111
1 vote
1 answer
49 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 ...
tomsu's user avatar
  • 111
1 vote
1 answer
64 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 ...
Marisa's user avatar
  • 23
0 votes
1 answer
196 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,...
Sicilian-Najdorf's user avatar
1 vote
1 answer
128 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 ...
Arie S.'s user avatar
  • 13
1 vote
2 answers
762 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 ...
KaliTheGreat's user avatar
1 vote
1 answer
170 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 ...
Sicilian-Najdorf's user avatar
0 votes
0 answers
84 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 ...
dezdichado's user avatar
0 votes
2 answers
86 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' ...
user avatar
1 vote
2 answers
128 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 ...
Morgeth888's user avatar
1 vote
1 answer
44 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 ...
Anwesh Budhathoki's user avatar

1
2 3 4 5
40