Questions tagged [query-optimization]
For questions about making database queries run faster. PLEASE INCLUDE QUERY PLANS, AND TABLE AND INDEX DEFINITIONS in your question. Please use additional tags to specify your query language and server maker. Some of these questions may belong on https://DBA.stackexchange.com, especially if they involve reindexing, query plans, etc.
query-optimization
11,565 questions
3
votes
1
answer
105
views
High SQL Server parse and compile time with UDF
I am working on a simple user defined function. It just accepts a varchar(100) parameter, does some string manipulation (nothing more complex than SUBSTRING, CHARINDEX, REPLACE etc. and returns a ...
- 53
0
votes
0
answers
11
views
Under what conditions does a large IN filter cause performance degradation in Milvus vector search?
I am using Milvus to perform vector similarity search combined with a large ID-based filter, and I am concerned about query performance when the filter list becomes very large.
My setup is as follows:
...
- 41
1
vote
0
answers
30
views
Why is clustering on _id not reducing bytes scanned in BigQuery for a table synced via Datastream?
I have a BigQuery table (approx. 140 GB in size) that is synchronized from MongoDB via a Google Cloud Datastream. I have set the _id column as the clustering ...
- 11
3
votes
2
answers
74
views
MariaDB 10.4 Query Performance on two similar tables: from seconds to hours
I'm experiencing significant performance differences between two similar queries in MariaDB 10.4, and I'm looking for optimization suggestions.
Problem Description
I have three tables with similar ...
- 1,145
Best practices
0
votes
15
replies
124
views
Any advice on Inner Join query performance
I have a T-SQL query shown here which is taking forever to run.
I am trying to see if there is a better way to write the Inner Join statement. The source table has ...
- 1,334
0
votes
1
answer
81
views
Should SQL filters be applied before or after vector similarity search in PostgreSQL
PostgreSQL tables sometimes store embeddings alongside relational columns and run vector similarity search with additional filters.
Example schema
...
Best practices
0
votes
3
replies
118
views
Do SQL databases apply WHERE filters before joins?
from a in xx
join b in yy on a.someprop equals b.someprop
join c in zz on a.someprop1 equals c.someprop
where a.someprop2.Contains(strval)
I’m trying to ...
- 175
0
votes
2
answers
244
views
Inner join performance issue
I'm experiencing a performance issue with the following query, specifically caused by the join condition
AND edh.EmployeeCode = e.EmployeeCode
After analyzing the ...
- 17
3
votes
2
answers
156
views
Can PostgreSQL optimizer carry `ORDER BY` out of `JOIN` subquery?
There is a "theoretical" query:
SELECT * FROM a
JOIN (
SELECT b.pk, b.not_pk
FROM b
ORDER BY b.not_pk
) AS b2
USING (pk)
and ...
- 435
0
votes
0
answers
81
views
How to achieve dynamic partition pruning in BigQuery TVF using a subquery lookup?
I have a Table Valued Function (TVF) that takes an array of IDs as input.
source_table: A very large table (TBs) partitioned by day on ...
Tooling
0
votes
3
replies
63
views
How to enforce dynamic partition pruning in BigQuery TVF using a subquery lookup?
I have a table-valued function (TVF) that takes an array of IDs as input.
source_table: a very large table (TBs) partitioned by day on ...
0
votes
0
answers
105
views
How to update a large number of mongodb documents in a tree structure?
I'm using mongo db with payloadcms and I have tree like structure.
We have a collection called nodes. A node have children as array and a parent id.
Now I want to change the node status like marking ...
1
vote
1
answer
154
views
3
votes
1
answer
119
views
Cause of and means to avoid "flat BNL join" in mariadb 10.11
I'm trying to help a user in another stackoverflow question and have bumped by head into a strange behaviour.
As I rarely use MariaDB, I'm opening another question to investigate the behaviour,
...
- 87.7k
1
vote
1
answer
70
views
AWS Glue Script Scanning Entire Table Despite Date Filter
I have written a small Glue script that fetches some data between two dates, but I found that it scans the entire table instead of just the data within the specified time range. I also tried creating ...
- 11
Advice
0
votes
6
replies
105
views
Why MariaDB doesn't use newly added index?
MariaDB version is 10.4.34.
The query looks like:
...
- 18.8k
0
votes
1
answer
219
views
Make Postgres query fast without UNION
The ORDMBS is PostgreSQL 17.5 on x86_64-suse-linux-gnu, compiled by gcc (SUSE Linux) 7.5.0, 64-bit.
I have big table (about 150 GB), partitioned.
Full table description:
...
2
votes
2
answers
142
views
Pros and cons of `INSERT INTO` versus `UNION ALL`
I am working with an Oracle database in which each year's data is stored in different tables. I am not the DBA so I cannot change that. Also, I do not have the permission to consult execution plans or ...
- 553
3
votes
0
answers
134
views
Azure Cosmos: MongoDB sharded cluster: $in query performance degrades with fewer values
I'm facing a counter-intuitive performance issue with my MongoDB sharded cluster where queries with fewer values in an $in clause are significantly slower than queries with more values.
The Issue:
...
- 31
0
votes
3
answers
136
views
How to update by the other GROUPed table values effectively?
I have the tables Invoices (with No_ key) and the related InvoiceLines (with ...
- 21.1k
-4
votes
1
answer
67
views
How to precompute nested date ranges efficiently to optimize range filtering and pagination? [closed]
📝 Body
I have a Mongo collection CollectionA where each top-level object contains a nested array of meetings now each meetings have start and end times, for example:
...
- 51
0
votes
1
answer
161
views
SQL query produced by Entity Framework very slow
I have the following query being generated by EF:
...
- 1,241
-1
votes
2
answers
246
views
Azure SQL S0 Tier – Slow Update Performance During Peak IoT Data Processing
I'm using Azure SQL (Standard S0: 10 DTUs) to store data from various IoT devices.
Each IoT device sends messages to my Azure IoT Hub, which then triggers an Azure Function App. The Function App ...
- 763
0
votes
2
answers
225
views
Why correlated scalar is 10x times slower in MySQL comparing to PG
Let's create a table with 3000 rows
...
- 773
3
votes
1
answer
208
views
Why does this query slow down this much over time
I'm testing with a very simple database queue to see how fast I can process it. At the moment without even doing anything else then just retrieving it from the queue.
A simple CTE query selects one ...
- 14.3k
4
votes
1
answer
187
views
Why does 'Index Seek' read 20 million rows for `select max(uk) from T where uk <= @x`?
In a table T with 20 million rows and a unique constraint on integer column uk, the following query induces SQL Server 2019 to ...
- 4,938
1
vote
1
answer
105
views
MySQL 8 runs very slow when including a NOT IN in the where clause
I have a website that displays on the main page the 12 latest uploads from the site's users. In that query, users can filter out uploads from certain other users (hence the NOT IN clause). Here is an ...
- 61
2
votes
2
answers
358
views
Writing better compound indexes
I recently came to know about the performance boosting capabilities of compound indexes or composite keys in ...
- 2,087
1
vote
0
answers
50
views
Multiple language labels in sparql query on wikidata
I want to query wikidata entities and their labels in multiple languages. But for some reason querying the labels is very inperformant.
My base query looks like this (find 3 life forms that have ...
- 830
1
vote
1
answer
81
views
SQL - Adding Union All on derived table subquery fetches all rows
Attempting to optimize a portion of a query that is joining two related tables, and getting odd results compared to other queries in the project with similar structures. Here is a very simplified ...
- 11
3
votes
1
answer
127
views
What is the optimal BigQuery SQL query to group the same data multiple times over different groups?
I have a very large BigQuery table with web events that I want to aggregate into daily, weekly and monthly numbers of visitors. And I want to join them into the same table.
My initial guess is to do ...
- 35
2
votes
2
answers
162
views
0
votes
1
answer
106
views
Implementing CASE-WHEN or Aggregations in Materialized Views in BigQuery
I have the below mentioned BigQuery SQL for a View. However, both the tables used in the query are huge in volume and hence I am facing terrible performance issues.
If you'd glance at the query, I am ...
- 895
2
votes
2
answers
126
views
Mysql performance issues
we have some tough performance issues that seem to us that they shouldn't exist, but we don't see any way of solving them.
We have a Gcloud Mysql database, with 4Cpu, 16go Ram. The full instance ...
- 304
5
votes
3
answers
119
views
Requesting an example where query with BRIN index runs much faster than with BTREE
It's very easy to see that BRIN indexes require orders of magnitude less space than BTREE indexes.
However, I'm struggling to come up with a query where elapsed time with BRIN index vs BTREE index is ...
- 773
0
votes
1
answer
100
views
MySQL 8 slow queries on indexed table with WHERE event = ? AND merchant = ? AND created_at >=?
I’m troubleshooting slow queries on MySQL 8 and need advice.
I have a table salla_events (~1M rows). The queries look like this:
...
- 11
2
votes
3
answers
234
views
Create multi table index/composite key
I have a query which contains 3 tables. If the fetched data length is small (about 50,000) then it works very fast (nearly under a second), but when the data starts exceeding it becomes slower (9 ...
- 2,087
1
vote
3
answers
112
views
Insert value into mysql if this value isn't the latest value
As an example, imagine I want to log the outside temperature in a table. It is read every minute but changes only 20 times a day or so, hence I don't want useless entries every minute. A value shall ...
- 456
3
votes
1
answer
99
views
Why does my query get slower and not faster when I constrain the data set?
I have a TimescaleDB table that records activities of various devices. This is a huge table and any interactions with it are normally expensive. Devices can be enabled as a group, and activation has a ...
- 16.2k
2
votes
2
answers
128
views
Optimize big MySQL table for faster MAX query latency
I have the following table in my MySQL DB:
...
- 77
-1
votes
1
answer
130
views
SQL update query where table contains NVARCHAR(MAX) held in LOB [closed]
I have a SQL script that updates data in various tables. It used to run quickly but is now slowing down.
The tables being updated have NVARCHAR(MAX) columns and ...
- 4,944
1
vote
3
answers
105
views
0
votes
1
answer
303
views
How to optimize slow TypeORM find() query with multiple nested relations in NestJS?
I am working in the backend of an application. One part of this application (like every application nowadays) is using AI for multiple things. The application's main purpose is building data warehouse ...
- 23
4
votes
2
answers
179
views
Catastrophic query planning, non-use of index in MySQL 8+
I have a legacy Drupal 7 site that is used strictly internally. It's replacement is under active development, but in the meantime it is using Aurora2, MySQL 5.7 compatible and is in very expensive ...
- 549
0
votes
2
answers
186
views
MariaDB left join ignores index key - slow performance with large joined table
I have a select with a large joined table. On local, performance is fine, but on my server it takes much longer. Below are the version infos (I just upgraded to MariaDB 11.4 on the server).
If I do an ...
- 3,558
5
votes
4
answers
233
views
How to speed up MySQL query containing LEFT JOIN and GROUP BY with 4M+ rows?
I have the tables "projects" (362K rows) and "projects_employees" (4.27M rows), one-to-many. I'm trying to get aggregated data for every employee and it takes 6-7 seconds. Is there ...
- 51
2
votes
2
answers
202
views
Skip Take performance issue
I am coming across a strange issue with EF Core 8.017.
I have a query that searches a table of document metadata. A document's text is a column with a full text catalog that is queried using the ...
0
votes
1
answer
188
views
How to delete large number of records from a table faster in plsql?
Table #1:
buck number(10),
sname varchar(20),
...
total of 20 columns
Table #2:
...
- 3
2
votes
1
answer
35
views
Why do we need to read the previous offset data when using a secondary index?
I'm learning SQL optimization of deferred joins from this article(https://hackmysql.com/deferred-join-deep-dive/).
The author uses a simple example to explain. Presume pk is the primary key column, ...
- 23
1
vote
1
answer
63
views
Querying Many-to-Many relations in Prisma
I have following schema.prisma generated from existing db structure:
...
