Questions tagged [optimization]
In the context of a database, optimisation refers to the process of the query optimiser selecting an efficient physical execution plan.
1,950
questions
0
votes
1
answer
40
views
How to optimize a query in postgres?
I have three tables: company, employments and entries. A company has many employments and a single employment has many entries.
The entries table has the most items in the entire database (currently ...
0
votes
0
answers
38
views
How can I optimize the view in MariaDB for Order By?
I have a list of products. Each has many tags. Those tags have normal tables, but they also have OLAP tables that present CSV for each product.
For example, a chair can have 'furniture', 'wood', and '...
- 1,343
0
votes
0
answers
13
views
MongoDB indexing for dynamic query
I'm trying to build a query optimizer for survey response data, all questions are stored in a collection and have some fields are number_of_selection, option_marked etc.
Now I'm trying to build a ...
0
votes
0
answers
32
views
Faster running for this query sql server
i used database engine tunning advisor for creating some index and statistics but still run slow
SELECT
C.code_commande AS 'Order',
C.code_client AS 'Cust.',
...
3
votes
1
answer
87
views
PostgreSQL Query Planner overestimating Row Count leads to slow Hash Join
I have a table with two columns and about 10M rows. About half the records are NULL in the first field, and about half the records are NULL in the second field, although there are a few hundred ...
- 31
0
votes
0
answers
35
views
PostgreSQL Optimizer does not find best solution when specifying subset of keys in query
PostgreSQL (Version 15) does not find a (in my opinion) obvious optimization for the query plan, is there a way to help the optimizer with this problem?
I have a specific kind of query where I have to ...
- 9
5
votes
1
answer
162
views
Partitioned view aggregation query not optimized
Given the following two tables:
CREATE TABLE SalesLedger (
Id int PRIMARY KEY IDENTITY,
Date date NOT NULL,
Total decimal(38,18),
INDEX IX (Date, Total)
);
CREATE TABLE Purchases (
Id int ...
- 11.4k
0
votes
1
answer
39
views
Help with SQL Query: Is there a cost-efficient way to join multiple tables to one main table?
I have a main table set up like this:
id
table_to_join
table_item_id
1
products
123
2
products
577
3
products
344
4
products
1230
5
images
14
6
images
42
7
video
555
8
products
400
9
video
9
...
- 33
0
votes
1
answer
71
views
How to optimise a query with inner join and order by
I'm struggling with optimising my query. I've tried to solve the issue with various indexes by none of them seems to be helpful.
There are 3 tables: transfers(id, amount, type, timestamp, file_id), ...
- 101
1
vote
1
answer
31
views
Slower MySQL queries involving both wp and custom table
Below are less efficient MySQL queries involving wp posts, taxonomies, and a custom movie table search. The custom movies table contains around 30k rows, and wp posts have been generated with the ...
- 141
-2
votes
1
answer
25
views
Database for video from the internet [closed]
I am an direction student from India working in advertising. For making treatment note and pitch deck I require a lot of photos for referencing from different videos from the internet readily ...
5
votes
1
answer
190
views
Single Predicate Check Constraint Gives Constant Scan but Two Predicate Constraint does not
I can create the following constraint on the AdventureWorks table Person.Person:
ALTER TABLE Person.Person ADD CONSTRAINT ConstantScan CHECK (LastName <> N'Doesn''t Exist')
This tells SQL Server ...
- 1,898
0
votes
2
answers
41
views
Optimizie MySQL query: col_name IN (val1, ..., valN)
I had posted a question about mysql equality range optimization here.
At this post, I want to discuss about why mysql optimizer is too bad for this type of query: col_name IN (val1, ..., valN).
My ...
- 145
2
votes
3
answers
374
views
How to manage mysql database table index
I am not aware of how to manage an mysql database table index and I attempted to implement it, but I am uncertain about managing the index for this extensive MySQL database table (Will do it at my end ...
- 141
0
votes
1
answer
14
views
Xamarin.Forms Working with Data
I'm looking for the best option to work with a database for a mobile app. The data that I'm looking to store is articles, posts , sounds (podcasts) and pictures.
I have 0 knowledge (as to 2023) ...
0
votes
2
answers
57
views
Moving tables to different database within same sql server
There is a SQL server, there are around 100 databases in it. I have to query a few tables from one of the databases. when I query, it's very slow and I think CPU utilization is very high at that time. ...
- 101
1
vote
2
answers
292
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....
2
votes
1
answer
82
views
random_page_cost and memoize plan relation
Postgres 15.2. I have the following simple query:
SELECT mark.last_modified FROM mark
INNER JOIN element ON mark.element_id = element.id
INNER JOIN model ON element.model_id = model.id
WHERE ...
- 135
2
votes
1
answer
237
views
How can I use efficient index seek to find the latest row filtered on a small subset of rows?
I have a transaction table with transactions for accounts. I want to fetch the latest transaction for a (small amount of) accounts. If I only filter on one account, the DB will use efficient index ...
- 479
3
votes
3
answers
136
views
Ordering on two columns is less efficient than order on only the first, why?
I have a table that stores transactions for accounts, and I want to select out the latest transaction for a specific account. But the DB (Azure SQL) seems to fetch all transactions for the account, ...
- 479
0
votes
1
answer
53
views
Cardinality miscalculations leads to ridiculous execution plans
I'm not talking about stale statistics, or just simply "bad"/"non-optimal" plans.
We have a lot of complicated queries running in our database. Normally all works as expected. But ...
1
vote
1
answer
102
views
Index Seek Results in More Logical Reads in SQL Server
I have the following table and data in it. I have two indexes on it: One that contains only a few columns from the where clause and the other one contains all the columns from the where clause.
I'm ...
- 435
1
vote
4
answers
87
views
Can this batched insert be optimized with a #TempTable?
I have this task of importing rows from a source table to a destination table while doing some mapping of columns on the way. The rows are identified by a GUID, and only rows not already present ...
- 165
0
votes
1
answer
40
views
MySQL gets slow before optimizing table
We have a custom made webshop built on the Laravel PHP framework which processes, on a busy day, 7000 orders a day. Since all orders are sent to a third-party point of sale system via an API, the ...
- 1
0
votes
0
answers
26
views
Advice about how to optimize DB setups when using Docker containers and Docker Compose
I have some general questions about how best to build and manage DB servers (Postgres and MySQL mostly) when using Docker builds with docker-compose.yml. In the past, I've sometimes bound the MySQL ...
- 103
0
votes
1
answer
62
views
What strategy should I use for my gigant MYSQL Tables?
I'm working in a system that is using MySQL as primary DB.
One of the biggest tables is almost 1TB in size, others are around 100-300 GBs.
Queries started to be really slow.
I search some info, and I ...
1
vote
1
answer
107
views
Which SQL Server component is in charge with checking cached execution plans for a given query?
I understand that whenever SQL server receives a query it looks in the cache plan for any matching execution plans for that query. However, most of the resources that I encountered don't specify ...
- 131
1
vote
1
answer
103
views
Postgres 14 WAL FILE cannot change min wal size(in WAL DIRECTORY) after changing propperties min_wal_size max_wal_size
I'm completely new in Postgres optimization. I was reading a lot about checkpoint and WAL files. I found a query to examine the ratio of checkpoints time to requested (exceeded WAL max size)
SELECT ...
- 11
2
votes
0
answers
69
views
Why is it so fast to create an index on a table?
While optimizing my query in Postgres, I noted that creating an index is always very fast, less than 1 second on a table with millions of rows.
But if there's no index, a query would take a few ...
- 285
1
vote
2
answers
46
views
How to optimize this query or these many indexes?
What am I doing wrong here?
I will post the table structure and indexes first, then the query that is slow, and many others that are very fast (instant).
How to optimize that one query to run faster ?
...
- 195
1
vote
1
answer
30
views
Could cloning / imaging SSD cause MySQL data to be more fragmented?
On a MySQL server (InnoDB tables) with /var/lib/mysql around 350 GB, I cloned the nvme SSD (using Macrium Reflect) in order to compare query speeds with a faster SSD. But queries are around 4 times ...
- 195
2
votes
2
answers
847
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 ...
0
votes
0
answers
246
views
MySQLTuner vs cPanel SQL recommendations are different / contradictory
I've used WHM's (cPanel) to optimize the mysql configuration on my server and it suggested making the following changes which I made
innodb_buffer_pool_size=3328M
key_buffer_size=55M
...
- 51
1
vote
2
answers
169
views
Delete query takes a long time - table spool and hash match in query plan
After upgrading from SQL Server from 2012 to 2019 we have encountered problems with very simple delete query like:
DELETE [_sales].[OfferDetail] WHERE [OfferDetailsID]=@1
Operation takes about 15-30 ...
- 111
0
votes
1
answer
187
views
How to Get a PAGELATCH_SH in sql SERVER tempdb
I am learning memory optimization in SQL server . On the way I learned about PAGELATCH WAIT .But I don't know how it happens and I don't know how to Test it with optimisation .
Can someone give me an ...
- 89
1
vote
1
answer
48
views
PostgreSQL: The actual rows number of Materialized Merge Join Node does not inherit the inner node
I found a plan to perform strangely. The actual rows number of Materialized Merge Join Node does not inherit the inner node, but is consistent with the parent node. The visualization of the plan is ...
- 11
0
votes
1
answer
350
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,
...
- 101
0
votes
1
answer
65
views
Help me in optimising the query which takes almost 50 seconds
Please help me optimise the following query running on MySQL 8.0.28 Community Edition.
explain select id,
created_at,
data,
deleted_at,
entity_id,
...
0
votes
0
answers
38
views
Designing a scaleable social photo gallery in Postgres
I’m trying to design a scaleable approach to modeling a social photo gallery and user profiles.
Every profile will have 1 gallery.
My current idea is the following:
Photo table (id, photourl, fKey ...
- 101
1
vote
0
answers
135
views
Optimizing UPDATE multiple tables with specific commit size
I'm trying to optimize my procedure for updating multiple tables with huge amount of data. (millions of rows).
The commit size is set to 50.000 to fit memory capacity.
Here is what I have now. The ...
- 11
0
votes
1
answer
153
views
Postgresql perform sequential scan instead of index scan for indexed column
I'm currently using PostgreSQL 14.0 for windows
My target table has a 900k+ rows and my query is using single column filter (status_id).
The column status_id is indexed via btree (default)
When i ...
0
votes
2
answers
460
views
MariaDB - Mysterious Delay at Each Query
I have 2 tables on MariaDB (10.5.16-MariaDB-log) where I query table A, and then loop over the roughly 4000 results and for each one do a query on table B. (This is inherited code and this will be ...
- 11
0
votes
0
answers
59
views
MySQL occasional performance issues
I am currently working on system with MySQL DB and SpringBoot (Hibernate). Both are located on the same server. All tables use InnoDB. Although there are no slow queries (5+ seconds), there is ...
- 1
1
vote
2
answers
171
views
Lots of "key value" joins causes SQL Server query to exponentially slow down
I am migrating some data that's stored "key value" style, from an attributes table that uses an Object ID and an Attribute Type as a Clustered Index (I've also tried as a nonclustered index):...
- 133
0
votes
1
answer
86
views
Does it make sense to enlarge "shared_buffers" for writing Postgresql?
I'm using Postgresql-11, with a traditional HDD as the physical storing device.
I need to optimize the performance for writing.
I have changed the wal_buffers from -1 to 512MB, that means that ...
- 285
1
vote
1
answer
86
views
Is there any configuration not to use index merge in mariadb, if a cardinality of an index is below than that?
There is a table have 2 indexes, called state and CATEGORY, which has a very low cardinality respectively 4 and 24 within 7,110,590 rows in the table.
When running query of select statements that ...
- 111
3
votes
2
answers
262
views
Tuning Postgresql for writing of a non-critical DB
The DB is being used to store market quotes, so that losing data of one day is acceptable.
All writing operations are performed at a fixed time of a day, serially, by single connection.
The ...
- 285
0
votes
1
answer
61
views
How to tell if a MySQL query speed is bottlenecked by storage or CPU speed?
Using MySQL 8.0.30 on Rocky Linux 9
For slow MySQL queries in general, not for a speciffic one, is there a way to tell if query speed was bottlenecked by storage speed, cpu speed, or maybe even ram ...
- 195
0
votes
0
answers
46
views
Optimize SQL Query
Im having trouble with the performance of this query, especificallly with the case statements. How would yo optimize it?
SELECT a.t,
a.c,
a.id,
a.id_r,
a.s,
a.n,
a.n_s,
a.c,
a.co,
a....
- 1
0
votes
0
answers
95
views
Better way to store stock levels for products?
I currently have 3 tables: locations, products, and stock, which are pretty self-explanatory:
The fields of stock are location_id, product_id, and quantity, which is great... until I add a new ...
- 111