Skip to main content

Questions tagged [query-performance]

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

Filter by
Sorted by
Tagged with
0 votes
1 answer
12 views

How to Index/Query JSONB Columns with Arbitrary Keys for Efficient Membership Queries in PostgreSQL?

In Postgres, how do I properly index/query jsonb columns that only contain key-value pairs where the keys are arbitrary, to speed up membership operations? I have a table structured like this: CREATE ...
0 votes
1 answer
78 views

Simple query taking way too long

I have a simple query, using an indexed column, on a very small data set, returning 1 row, hitting Azure SQL Database - taking over 1/2 second. Another earlier execution of this query took 38ms. ...
0 votes
2 answers
797 views

MySQL - Simple update is very slow and causing high load average with large amount of server

Simple update is very slow and causing high load average with large amount of server : table name : CREATE TABLE IF NOT EXISTS `LISTE_PROMOTION3` ( `id_list_prom` int(11) NOT NULL AUTO_INCREMENT, ...
0 votes
3 answers
1k views

MySQL Connections slows down my website

I have a website that runs perfectly normal, until today it becomes very slow. After a few hours debugging, it all points down to database. I ran these queries show status like 'Connections'; +-----...
1 vote
3 answers
1k views

SQL Server Disk throughput slower than diskspd

This is a bit of a broad question but I am trying to understand a storage performance behavior with two of our servers. I was following https://www.brentozar.com/archive/2015/09/getting-started-with-...
1 vote
1 answer
72 views

Trouble with slow stored procedure performance on 35M rows with CTE/Temp table

We recently went through a new feature release to our app that added about 25 new columns to a large table (~35 million rows), and now we're having some major query performance issues. I assume that ...
0 votes
0 answers
18 views

Postgresql calculate SUM value in time for all items in a table with gaps

I would like to calculate SUM value in time for all items in a table "measures", which contains measures from stations (each one defined with an identifier) _ id_station (varchar) _ ...
0 votes
1 answer
324 views

Large dataset of 400+ million rows to compare

I am trying to solve this logic problem but my current MySQL schema is giving me a very poor performance with a large dataset. I have a table which contains almost half a billion telephone records. ...
0 votes
1 answer
760 views

Bulk SQL INSERT into Azure SQL Database using spark causes blocking/contention?

I am running the following code using microsoft's sql sparkconnector to write a 1-2 Billion dataframe into Azure SQL Database. df.write \ .format("com.microsoft.sqlserver.jdbc.spark") \ ....
0 votes
0 answers
15 views

Working around Materialize node in PostgreSQL explain plan

I'm currently trying to optimize the following query for a school assignment: SELECT f.aircraft_code, s.seat_no, SUM(tf.amount) total_amount FROM seats s JOIN flights f ON s.aircraft_code =...
0 votes
1 answer
716 views

Fastest way to check if record is related to a particular user in postgres

While implementing a sharelikes table I added a procedure to add a new record for a like. I wanted to add a check before insert that the item being liked does not belong to the users who submitted ...
0 votes
1 answer
29 views

Manage the amount of data on tables

I can have several tables with 10+ millions rows in by year. I would to know what's the best approach to maintain responsiveness with this amount of datas and I have two ideas. Don't touch these ...
0 votes
2 answers
53 views

PostgreSQL uses slow Index Scan instead of Bitmap Heap + Index scan with ORDER BY and specific LIMIT

Given the following table: CREATE TABLE chat_message ( id bigint DEFAULT nextval('public.chat_message_id_seq'::regclass) NOT NULL, "user" integer, type smallint, text text ); ...
1 vote
2 answers
830 views

mysql - large table - index performance and query issue

I have simplified a much larger table below: CREATE TABLE `core` ( `id` int NOT NULL, `loc_country` enum('United States','Colombia','United Kingdom', 'Australia','India','Germany','...
0 votes
1 answer
338 views

Speed-up lookup of a row with prefix pattern

Say I have these rows in one UNIQUE column: 202101XXXXX 202101XXXXX 202102XXXXX 202102XXXXX 202103XXXXX 202103XXXXX The first 6 digits are year and month (YYYYMM), and the XXXXX part are random ...
0 votes
0 answers
12 views

Variable time execution for INSERT/UPDATE

I do an INSERT/UPDATE in a table who has around 1+ million rows in MySQL 8.0. I don't know why I have different execution times for the same data to insert. For example, I try to insert exactly the ...
2 votes
2 answers
2k views

Optimizing a recursive CTE or replacing it with a temporary table

I have a recursive query like this: with recursive PCte(id) as ( select p1.id from Product p1 where p1.parent_id is null and p1.serial_number in ('123', '124','125', 'n') union all ...
4 votes
2 answers
3k views

Dealing with suboptimal postgres query plans related to nested loops

I'm developing an application that dynamically builds up SQL queries and executes them against postgres 13. Some of the queries are very slow because they use nested-loops but the query planner ...
0 votes
2 answers
1k views

PostgreSQL 10 Optimizing slow query performance

I have the following query: select i.id as id, i.ts_updated as updated, i."name" as name, cast(attr.data_norm as text) as data_norm, cast(_59.json_agg as text) as _59, ...
7 votes
2 answers
10k views

What is the most efficient way to count the number of rows in a table?

I am using Postgres with the following query: select count(*) from image; The primary key on this table is non-incrementing; it's a unique serial number for the images stored in the table. Our app ...
1 vote
2 answers
414 views

Need help in optimizing a slow Postgres Query

So, I need to calculate a rolling Monthly active users on everyday for last 100 days. In output, one column should represent the dates of last 100 days from today and against every single day, number ...
1 vote
1 answer
659 views

Optimize join without denormalization

I have two tables: orders(order_id, scan_timestamp) and order_products(order_product_id, order_id, pick_number, quantity) and I want to know how many of a particular product (identified by ...
0 votes
2 answers
344 views

Optimizing querying for any of multiple terms in string

I have been pondering what would be the most efficient way to search for any of multiple urls contained in an unstructured text field. Suppose I want to find posts containing either https://...
0 votes
1 answer
283 views

Improve sqlite mapping query

I have an SQLite DB that has couples of tables. one of them looks like this: (id integer primary key, location integer, name text, desc text, version integer) select id,other_colum from table where ...
-1 votes
1 answer
24 views

How do we optimize a transaction-log VIEW in MySQL that will use a LIMIT when executed?

Problem / Observation A query that performs well outside of a view (with a limit 20), uses a suboptimal query plan when placed inside of a view if the limit 20 is not included in the view definition ...
0 votes
1 answer
49 views

How to optimize query with multiple many-to-many relationships

I have a performance issue when querying an events table that has over a million rows. Our application logic stipulates that users can only access events that they have access to via their 'groups'. ...
0 votes
1 answer
32 views

Question related to Database Design in SQL Server

I have two tables: Products and ProductVersions. The table structures are as follows: Products Table: ProductId RegionId ActiveProductVersionId isDisabled ApplicationId ProductVersions Table: ...
0 votes
1 answer
414 views

Openquery through Oledb driver for DB2 slows down after a while - reboot only solution?

We have several sql servers that read from a db2 database on an as400 via Openquery to a linked server using the Microsoft oledb driver for db2 v5. One sql instance had a problem where its selects ...
1 vote
2 answers
788 views

MongoDB - Slow aggregate query

It takes around 6min to get the result from MongoDB, when I use the following aggregate query. db.barcodes.aggregate([ { $lookup: { from: 'company', localField: '...
2 votes
1 answer
36 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 ...
0 votes
1 answer
59 views

How to Make Queries on a DATETIME Column Efficient If My Primary Query Pattern is an Hour?

Context Here is the DDL that I am intending to use to define the table for a logistics/delivery company. CREATE TABLE scraping_details ( id INT IDENTITY(1,1) PRIMARY KEY, -- Identity insert and ...
0 votes
0 answers
40 views

Erratic Performance of CLUSTER in PostgreSQL

I work with PostgreSQL 12 and have partitioned tables that I am trying to cluster. Each partition is roughly the same size in terms of GB. However, the performance can vary significantly, ranging from ...
0 votes
2 answers
1k views

Limit rows in Correlated Subquery

I have one big and two small issues with my query I need help with. First, I want a SQL (postgres if it matters) query that gets SUM of last two ratings of fruits and makes a new column for each fruit ...
1 vote
1 answer
1k views

Optimal way to SELECT all rows in a table that are not referenced by a foreign key?

In MySQL I have a table "resources" that represents a list of resources. eg: CREATE TABLE resources ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(100) NOT NULL, ...etc.....
2 votes
1 answer
989 views

Why does our MySQL server become non-responsive with the processlist showing tons of processes waiting in STATE=init, INFO=commit?

I work for a company that's been around for a while and has a large MySQL (5.6.48) monolith running in RDS. Recently, the database has starting going unresponsive for 10-30 minutes at a time during ...
0 votes
1 answer
903 views

Calculate the duration in an interval and outside of events that can last over several days

I need to calculate the total duration of events in a given time interval and on a given day of the week with PostgreSQL 11 For an event on the same day no problem. But over several days I manage to ...
0 votes
3 answers
383 views

Using JOIN to find duplicates in another table taking too long

Have a MySQL 8 database with the following tables. people id | mobile | name registrations id | people_id | other_stuff I am trying to find the mobile number and the number of times it's duplicated ...
0 votes
1 answer
308 views

High CPU Usage and MySQL Server Inaccessibility during Magento flat reindexing

We use the following configuration: Engine: Aurora MySQL v8.0 Instance Type: db.r6g.4xlarge vCPU: 16 Memory: 128.0 GiB Role: Writer Instance I'm encountering a critical issue during the reindexing ...
4 votes
1 answer
2k views

Why does postgres choose to sort instead of scanning the index?

I have the following table: # \d service Table "public.service" Column | Type | Modifiers -------------+----------+----------- customer_id | integer | not null date | date ...
0 votes
1 answer
634 views

Query goes in Copying to tmp table stauts in MySQL and never returns

I am getting some strange behaviors for my MySQL query status. Here is my query code: SELECT a.id as art_id, b.album_artist_id, s.song_title,s.song_seo,a.artist_seo,a.artist_name, b.album_title, b....
2 votes
2 answers
1k views

Optimising MySQL count() with INNER JOIN (550k rows)

I have the query below, which is currently taking approx 1 - 1.5 seconds to run in a MySQL 5.7 database. It's being called from a web application, and whilst I'll be implementing caching at the ...
1 vote
1 answer
110 views

Why does MySQL choose such a poor query plan for "IN (SELECT NULL WHERE FALSE)"

We have some code that goes through a SQL generator to produce parameterized IN clauses of the form WHERE id IN (?, ?, ?, ...) AND .... If the list is empty, the generator cannot issue IN () since ...
1 vote
1 answer
3k views

When do you need to run Analyze Table on MariaDB?

We have migrated a few large databases from earlier version of 10.1.22 to 10.6.7 - the latest provided by ubuntu repo for 22.04. All was well for a few weeks and then we started seeing very slow ...
3 votes
1 answer
2k views

What are the methods to paginate a table 100 rows at a time?

Let's say we have a table with a large number of records. Now to show all the data using pagination in a basic framework using a MySQL query, we can use limits to get a subset of rows: SELECT * FROM ...
0 votes
2 answers
337 views

Proper indexing for SELECT with OR condition

I want to get all the rows where sub string is in the first/last name. Then I want to order it by the exact match first and limit to 5 (pagination basically) Query works well until the table is ...
1 vote
1 answer
1k views

Subquery not using Index, even if forcing

Using MariaDB 10.5.12 on CentOS 7 If it makes any difference, my "optimizer_use_condition_selectivity" = 1 If I run: EXPLAIN SELECT COUNT(*) FROM ips WHERE ip="1.2.3.4" This query ...
0 votes
2 answers
539 views

large number of tables and postgres query performace

I have a scenario where there are 40000 tables in the database. but there are no huge unions. Very simple single table select queries are performing much slower than usual (table with just 600,000 ...
4 votes
4 answers
2k views

Actual and Estimated rows differ greatly

The full Actual plan is here. Prior to executing the plan (because I'm debugging a poorly functioning plan) I have this block of variable assignments: DECLARE @Days INT = 180 DECLARE @DateRangeFrom ...
3 votes
4 answers
288 views

Optimizing slow query

For a few days now, i'm trying to optimize this query. But still I haven't found the right solution in how to fix this. The contents table contains about 20 million records. As the connect table ...
1 vote
1 answer
3k views

MySQL does not use index, does full table scan instead

EDIT: See further developments below. I have an InnoDB table with the following schema: CREATE TABLE `data` ( `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, `app_id` CHAR(27) NULL DEFAULT NULL,...

1
2 3 4 5
62