Skip to main content
Share Your Experience: Take the 2024 Developer Survey

Questions tagged [query-performance]

For questions about improving the performance and/or efficiency of database queries.

Filter by
Sorted by
Tagged with
0 votes
2 answers
25 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
28 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
  • 1
0 votes
2 answers
57 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
1 answer
34 views

Postgres 15 column drop impact

Is the drop column operation scalable? Previously, we faced issues while updating a large number of records because it took more space. Is this the case with a drop column operation?
Slim's user avatar
  • 249
4 votes
2 answers
135 views

Index seeks and predicate push down

I have a query of the following form: IF EXISTS ( SELECT 1 FROM ( SELECT RowID, OETID FROM @InMemoryTableTypeTable i UNION ...
Dan Def's user avatar
  • 163
1 vote
1 answer
32 views

Oracle SQL query slow even with indexes

I have an Oracle 19c database (19.0.0.0.0), and two tables with lots of columns, I'll mention only the relevant ones: table1: createtime date contact_key char(96 byte) table2: contact_key char(96 ...
Gábor Major's user avatar
0 votes
0 answers
16 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
2 votes
2 answers
54 views

How can I find a topmost row and count the rows with only one scan?

Suppose that I have a schema like the following: -- Many rows CREATE TABLE t1(i INTEGER PRIMARY KEY, c1 INTEGER, c2 INTEGER); -- t1's rows with c1 even CREATE VIEW t1_filtered(i, c1, c2) AS SELECT ...
user570286's user avatar
3 votes
1 answer
42 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
1 vote
0 answers
30 views

Very slow to work with table using PostgreSQL [closed]

I have a table labelled SALES. It has indexes like this list "sales_pkey" PRIMARY KEY, btree (id) "sales_order_date_idx" btree (order_date) "sales_product_id_idx&...
David Lazar's user avatar
0 votes
1 answer
31 views

Get the statistics_stream on SQL server using SSMS

I have generated statistics for a few columns of a table using the following SQL command: CREATE STATISTICS [STAT_NAME] on [TABLE_NAME] (COLUMN_NAME) WITH FULLSCAN The generated statistics are ...
Mohammad Mosiur's user avatar
0 votes
0 answers
46 views

Azure SQL Database :: table size query takes forever to execute

I was working with the awesome Azure SQL Database Diagnostic Information Queries created by Glenn Berry and I realized that the query number 40 takes forever to execute: -- Get Schema names, Table ...
Francesco Mantovani's user avatar
1 vote
0 answers
49 views

Poor Postgres Query Performance with a Partitioned Table - pg_hint_plan does better

Simplified, I have a table with this layout: CREATE TABLE IF NOT EXISTS public.scans ( id bigint NOT NULL DEFAULT nextval('new_scans_id_seq'::regclass), geo geometry(Point) NOT NULL, ...
Eric B's user avatar
  • 11
0 votes
0 answers
26 views

How to tune below query

Please help suggest how to tune below postgres query. select distinct c.aid, jsonb_array_elements (cvp.value::jsonb) as somejson from cont c join compara_values cvp on c.id=cvp.cont_instance_id join ...
gdba's user avatar
  • 13
0 votes
1 answer
29 views

Does long running query cause replication delay in PostgreSQL which is in 'idle in transaction' state?

In my system I found same query called more than 30 times 'idle in transaction' state. Is there any way to to identify the reason of 'idle in transaction'? Should I terminate those query manually ...
Sheikh Wasiu Al Hasib's user avatar
-1 votes
2 answers
39 views

LEFT JOIN LATERAL with a function result, how to prevent unnecessary function calls?

I have a costly function and it only provides relevant data when certain conditions are met. That data is inserted into the result through a LEFT JOIN LATERAL as shown below. As it works now, the ...
Björn Morén's user avatar
2 votes
1 answer
131 views

How to query for aggregated streaks?

My goal is to detect price trends for cryptocurrencies. For that I'm getting OHLC pricing data for multiple currencies vs. multiple quote symbols (e.g. candles for Bitcoin/USD, Bitcoin/EUR, Litecoin/...
Sascha Mayr's user avatar
0 votes
1 answer
18 views

Convert character encoding in big database

We have big database (~1TB) and we need to update character encoding for all tables. I am running following SQL: SET FOREIGN_KEY_CHECKS = 0; ALTER DATABASE wallmob_etail CHARACTER SET utf8mb4 COLLATE ...
Justinas's user avatar
  • 101
0 votes
1 answer
34 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
1 answer
28 views

How Can I Efficiently Structure a Data Model for Handling Both One-Time and Recurring Tasks in a Task Management app?

fellow developers and architects! I'm in the process of designing a webapp aimed at managing tasks and recurring tasks, which can be seen as habits. The unique challenge I'm facing revolves around how ...
nanobot's user avatar
0 votes
0 answers
34 views

Query performance degrade after upgrade from MySQLv5.6 to v5.7

I upgraded the MySQL Server from v5.6 to v5.7 and since then I see query performance degrade. For example, I have the following select query that selects some columns and has a single column in the ...
msbeast's user avatar
  • 11
1 vote
0 answers
43 views

Selecting joined column kills index and results in very slow query

I recently added a JOIN to a query look up one of three names based on id. Normally this has little to no effect on performance but for some reason in this case when I SELECT the column in question ...
Eaten by a Grue's user avatar
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
0 votes
0 answers
24 views

What is the proper way to index status history tables in Postgres

We are using PostgreSql and have multiple tables which are laid out approximately like this. A table for some entity in our system apple id | created_at | updated_at | user_id An enum table with ...
Ethan McCue's user avatar
1 vote
1 answer
32 views

Many items in `IN` clause causing full table scan earlier than expected

We have a search index table, provided by a CMS, that is of the form: CREATE TABLE `craft_searchindex` ( `elementId` int NOT NULL, `attribute` varchar(25) CHARACTER SET utf8mb3 COLLATE ...
msbit's user avatar
  • 121
0 votes
0 answers
30 views

Performance - where clause not using index due to null verification, how do i use the index seek in this code without doing code duplication? [duplicate]

question : how do i use the index seek in this code, without doing code duplication? I've got this code : CREATE procedure [dbo].[GetActiveEntitiesByRecord] ( @MainId bigint , @...
dexon's user avatar
  • 47
3 votes
5 answers
843 views

Is COMPACT a better format for fixed length rows than DYNAMIC?

In MySQL / MariaDB I have this table with fixed-length rows (no VARCHAR, TEXT, etc) CREATE TABLE trigram ( id BIGINT(20) NOT NULL, trigram CHAR(3) NOT NULL COLLATE 'utf8mb4_general_ci', ...
O. Jones's user avatar
  • 563
0 votes
1 answer
78 views

Why is this PostgreSQL query with LIMIT so slow?

I have a .NET WebApi with two entities (please forgive the silly names, it's just an example): public class Father { public int Id { get; set; } public string Name { get; set; } = null!; ...
Alessandro's user avatar
0 votes
0 answers
9 views

KILL QUERY Flooding in General Query log for MySQL 8.0.25

Problem : We have two MySQL servers in master master replication. We can see many KILL QUERY statements in MySQL general log specific to stored procedures as below : 2024-03-20T21:54:45.056981+05:30 ...
Sana Ansari's user avatar
0 votes
1 answer
79 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
1 vote
2 answers
46 views

Select unique rows based on condition stored in another table

I want to select rows based on conditions that is fetched from the database. The tables structure are mentioned below. # Questions Table: ╔════════════╦═══════════════╦════════╗ ║ id ║ ...
Rohit's user avatar
  • 11
0 votes
0 answers
30 views

Performance Differences in JOIN query Between MySQL 5.7 and MySQL 8.0

Hello MySQL Community, I wanted to share some observations regarding the performance differences between MySQL 5.7 and MySQL 8.0, specifically related to join algorithms. In a recent project, I ...
Amarjit Singh's user avatar
1 vote
2 answers
175 views

Why OFFSET / FETCH causing Performance issues with Queries in MS SQL 2019?

I have this select query: SELECT ITEM.ID ITEM_ID, ITEM.CODE ITEM_CODE, ITEM.DESCRIPTION ITEM_NAME, ITEM.CODE_CATEGORY_ID CATEGORY_ID, ITEM_CAT.CODE CATEGORY_CODE, ...
Subin Benny's user avatar
1 vote
3 answers
70 views

How to filter by one column, and sort by another, using an index, in a query with a limit clause

Suppose I have the following table: CREATE TABLE posts ( id INT, num_comments INT, created_at DATETIME, /* Irrelevant fields omitted. */ PRIMARY KEY (id), INDEX (created_at), ...
Jonathan Wilson's user avatar
0 votes
0 answers
25 views

MySQL 5.7 on Amazon Aurora - how to read the IO Cache vs. Disk Read metric?

I'm trying to determine how often InnoDB is hitting RAM vs. disk. Does db.Cache.innoDB_buffer_pool_hits.avg mean InnoDB went to the buffer pool? Does db.Cache.Innodb_buffer_pool_reads.avg mean that ...
mstrom's user avatar
  • 133
1 vote
2 answers
39 views

Finding files a user has access to is slow when ordering

I'm working on a system where users are sorted into groups, and then those groups are applied on files. At a high level, what I need to do is to be able to pull out the 10 most recently-updated files ...
chraume's user avatar
  • 11
0 votes
1 answer
75 views

How does different forms of EXISTS() affect performance in MS Sql Server?

I've been told that when checking for the existence of a row in a table, this will work: EXISTS(SELECT 1 FROM....) but that a "better" way in regards to speed performance of the query would ...
Gharbad The Weak's user avatar
0 votes
1 answer
41 views

Distinct based on window function (sort curiosity or wrong problem approach)

A lot of values are stored in values. The data is in a hierarchical order, so there could be a probe, on which some measurements have been performed. Many points have been archived with strain and ...
lrahlff'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
2 votes
1 answer
125 views

MySql Aurora - how often to run ANALYZE TABLE?

I have a database that has existed for about 10 years, with hundreds of tables, many of which have several million rows. We don't have a DBA and we have an increasing number of slow queries. We're ...
mstrom's user avatar
  • 133
1 vote
1 answer
63 views

MySQL Amazon Aurora - a few slow queries causing extreme performance issues

I'm a dev, not a DBA, so pardon my ignorance. I'm running on MySQL Amazon Aurora, single-instance (with a replica for failover). A few days ago, my application had a handful of extremely slow queries (...
mstrom's user avatar
  • 133
-1 votes
1 answer
55 views

How to make this postgresql query to use composite index only ? Any Suggestion

Query: with firstData as (select rm.display_name as register, br.createdat, br.status, br.id as runid from bad_run br inner join bad_master bm on bm.id = br.badmasterid inner join regis_master rm on ...
ankush chauhan's user avatar
8 votes
0 answers
139 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
86 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
35 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
5 votes
1 answer
103 views

Improving FTS phrase search on postgres for common terms

I have a table defined like CREATE TABLE details_search ( id int4 NOT NULL PRIMARY KEY, "search" tsvector NULL ); CREATE INDEX details_search_idx ON details_search USING gin (search);...
g3rv4's user avatar
  • 153
1 vote
0 answers
79 views

Force index not working in Aurora MySQL 8

I have recently upgraded to Aurora MySQL engine version 8.0.mysql_aurora.3.05.2 from Aurora MySQL 5.7. I see huge performance issue after upgrading. Recently I found that I have queries whose indexes ...
Ali Mehdi's user avatar
1 vote
2 answers
46 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
69 views

Performance difference between multiple tables and indexed single table

I want to create a relational database for financial data and struggling to figure out what the best approach to this is. 1st option: single table for all records with an index 2nd option: one table ...
DamiToma's user avatar
  • 141
0 votes
1 answer
60 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
2 3 4 5
69