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
2 answers
2k views

MySQL db optimization error on innodb tables

I have a database named 'mysql' which is being used on RHEL 8 server running MySQL 8.0.32. When trying to optimize the entire database using the mysqlcheck command 'mysqlcheck -o mysql' the below ...
3 votes
1 answer
801 views

MySQL using variable in SELECT command with view affecting performance

sorry if this has been asked already. I've just moved from MySQL 5.6 to 5.7.17 and I'm struggling to understand this issue. I have a view "delete_me_test_" which has around 26k rows. SELECT ...
0 votes
1 answer
2k views

UNION ALL vs CASE - SQL query optimization

I need to retrieve the count of both approved and unapproved data from my_table. And I have tried both queries and succeeded. Which query is optimized and why. Please give suggestion DDL Query CREATE ...
0 votes
2 answers
12 views

PostgreSQL enforcing order dependent constraints within a query

Is there a best practice for enforcing the order in which constraints are applied? We have seen a few queries where the optimizer has reordered the results and passed rows through a constraint and/or ...
0 votes
2 answers
921 views

Optimization causes tmpdir to fill up with 'deleted' file handle

Using Percona 5.6.34-79.1. Storage Engine is InnoDB. One of the table to be optimized is 300Gb in size. tmpdir is a separate partition, and datadir is separate. datadir has 500Gb of space, so ...
0 votes
1 answer
421 views

Optimizing regex based query in sqlite

I'm using an sqlite database to store manually created labels for some data automatically queried from a live system. The data from the live system consists primarily of an address, comprised of 3 ...
0 votes
2 answers
420 views

SQL server alwayson 3 Node Primary Down

I am using three node alwayson Availability Group SQL 2012 Enterprise Edition. 2 Node in DC with File Share Witness quorum also in DC and third node to be setup in DR I need to perform a DR switchover ...
11 votes
2 answers
16k views

SQL Relationship between Subtree Cost and Performance Time

What is the general relationship between "SQL Final Subtree cost" and "Query time performance"? Example: When tuning a query and it goes from Subtree cost 0.2 to 0.1, does that ...
0 votes
1 answer
28 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 ...
1 vote
3 answers
97 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
41 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 ...
0 votes
0 answers
18 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
38 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 ...
4 votes
4 answers
2k views

Effect on execution plans when a table variable has a primary key

Having read a great deal about the differences between temporary tables and table variables in SQL Server, I am experimenting with switching from mostly using temporary tables to mostly using table ...
2 votes
2 answers
1k views

Optimize large table design

First, I am a complete novice when it comes to databases, but have been given the task to speed up queries on a large set of data. (Hundreds of millions of records) The current implementation is a ...
2 votes
2 answers
964 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
2 answers
36 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, ...
1 vote
1 answer
856 views

In Postgresql, can I create a column of a one-dimension array type with foreign key constraint? If not, how to simulate it?

In essence I have to design todo table, and an item table. I need to preserve item order, as well as allowing client to rearrange the order. If postgres allow an array of foreign key that would be ...
0 votes
0 answers
15 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 ...
0 votes
1 answer
2k views

MYSQL join on subquery optimization

This is a distilled version of the query that I am trying to run: SELECT * FROM table1 t1 INNER JOIN (SELECT * FROM table2 WHERE foo = 'bar') t2 ON t1.id = t2.id; The above query is incredibly slow ...
1 vote
1 answer
789 views

How to reduce redundant time-series data in MySQL into a function?

i have a question that actually does not fit here very much, but maybe it fits. So, i have data like this: How can i reduce this 3 entries with same values? Sure, i can just delete the other 2, but ...
0 votes
1 answer
873 views

Ways to optimize the PostgreSQL/TimeScaleDB query

What approaches I could take to optimize the performance of the following joinless query on the following PostgreSQL/TimeScaleDB table? So far, I managed to create the right index which is obviously ...
1 vote
2 answers
314 views

Index conditional aggregate query on PostgreSQL

How does one optimize a conditional aggregate query on PostgreSQL? A example of such query would be: SELECT SUM(score) FROM "games" WHERE "PlayerId" = 23 AND "status" = 'FINAL'; Indexing (PlayerId, ...
0 votes
2 answers
4k views

MySQL - How to optimize multiple OR conditions in a query on a large table

I have a table with the following structure: Configuration ( id: binary(16) Primary key, htId: binary(16), raId: binary(16), date, amount ); I have already created these indexes: ...
1 vote
2 answers
649 views

Database Design for Maintaining Supplier Invoice

I am not sure whether it is right to ask such question here. However, It comes to my mind this would be the best place to start. My needs are quite simple. As a distributor, I want to maintain all ...
1 vote
1 answer
2k 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 ...
0 votes
2 answers
494 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 ...
3 votes
1 answer
343 views

What exactly does PostgreSQL (or other databases) do internally when you "prepare" a query rather than just running it directly?

When I face a huge number of repeated INSERTs in a loop, I tend to first create a "prepare skeleton query" prior to the loop, and in the loop, I simply "execute" this prepared ...
0 votes
1 answer
30 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 ...
1 vote
2 answers
56 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 ...
0 votes
1 answer
29 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 ...
0 votes
1 answer
703 views

What is the most efficient way to check whether a BYTEA column contains data, in PostgreSQL?

We have an online platform where we sell eBooks. In the database, we have a "books" table, which contains the books' information. It also contains the book file, stored as BYTEA. In various ...
0 votes
1 answer
4k views

Costly HASH MATCH Aggregate

I have a view,which is slow when i query a simple select statement with a where clause.The where clause column is indexed(Non-clustered index) Here is the view and the plan. execution plan CREATE ...
3 votes
4 answers
286 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
1k views

MySQL: Performance issues in heavy multi-threaded environment

I have 23 of these tables in one database: CREATE TABLE foo1 ( DateTime datetime NOT NULL, BidOpen decimal(11, 5) NOT NULL, BidHigh decimal(11, 5) NOT NULL, BidLow decimal(11, 5) NOT NULL, ...
2 votes
1 answer
489 views

MySQL InnoDB or MySQL NDB Cluster which one to use

We have an application with 8000 users accessing in parallel(max). We are trying to bring in horizontal scaling. I came across MySQL NDB cluster. Should I go with it? Just for auto sharding and other ...
0 votes
1 answer
60 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 (...
1 vote
1 answer
52 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 ...
1 vote
2 answers
1k views

Live query statistic is waiting for query plan forever

I have a large query with performance issues. I want to run it in live query statistics mode, but instead of the execution plan, I see the inscription "Waiting for query plan". In sys....
1 vote
2 answers
993 views

MySQL indexing issue when trying to search for a part of a string/word

What we're running into: Engine: MySQL. We've been working on a filter where an user can search for a reference number. However, we've ran into an issue with performance on bigger data sets. We ...
0 votes
1 answer
569 views

What exactly happens when optimizer_prune_level = 1?

This documentation link states that optimizer_prune_level Controls the heuristics applied during query optimization to prune less-promising partial plans from the optimizer search space. Can ...
1 vote
1 answer
307 views

Parallel index-only scan with two sub-queries is slow

I'm trying to analyze a slow query that uses parallel index-only scan of two filters (using sub-queries). Specifically I'm the number of 'open_questions' that returned by either subplan1 OR subplan2. ...
5 votes
1 answer
7k views

Materialized Views and Indexes

Is there any way to force PostgreSQL to use an index that I created on a materialized view table? I tried every combination possible of moving the column attributes around to make Postgres to use my ...
1 vote
1 answer
336 views

effectiveness of creating index for ORDER BY column when used with many WHERE clauses

Say I am trying to build a pagination for a simple e-commerce app where user can search, filter, and sort items, and the result is displayed in an infinite scroll UI. I'm planning to use the cursor ...
0 votes
1 answer
988 views

Query using VIEW very slow when the same query is fast using WITH

Given this view CREATE OR REPLACE VIEW public."SellMeta" AS WITH wh AS ( SELECT "WWHH".id, "WWHH".url, "WWHH".name, ...
2 votes
1 answer
92 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 ...
0 votes
1 answer
278 views

Is it advisable to asynchronously update the summary table every time a transaction is deleted?

Given three tables: (a) Product (b) Transaction Head (c) Transaction Detail We need to create a query that fetches a list of products with associated latest ordered quantity and latest transaction ...
3 votes
1 answer
356 views

How to optimize simple table with groups and timestamps (SUM over 15M rows with GROUP BY)

I think I have quite simple question, but I couldn't find the answer anywhere. I'm using MySQL and I have a simple table: id timestamp groupId costA costB costC ... 1 2022-02-01 19:45 1 5,13 3,20 30,...
0 votes
1 answer
382 views

Difference between a query based on CTE and a simple query

i'm doing a select starting from a table named events that is joined with device measurements using a date between function (this time between is a sort of "mobile windowing") in order to ...
2 votes
3 answers
2k views

Backup Performance best practices

I'm in the process of re-writing our maintenance plans and at the moment the focus is backups. Whilst preparing this plan, I'd like to try and ensure that procedures are in place to ensure that backup ...

1
2 3 4 5
40