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,965
questions
1
vote
1
answer
132
views
Best solution for performance - to extract 400 over fields with varchar (300) for one single BI Data set
I have this requirement to extract about 450 fields with most of it's fields length with varchar(300) from multiple tables for one single BI report.
For better performance, I tried to create one ...
- 11
0
votes
1
answer
1k
views
MySQL not using index as expected with a WHERE IN clause
I have a table called mytable and I'm trying to optimize a query on it. I generated the index ind5 on it, and it has the nullable int column col_1 in first place. This query has a WHERE IN clause, ...
- 140
0
votes
1
answer
783
views
SQL query working with 8.0 but not working with 5.7 [closed]
I have a mysql table like this
CREATE TABLE `users_search_activity` (
`ID` bigint(20) UNSIGNED NOT NULL,
`user_id` int(11) NOT NULL,
`country_id` int(11) NOT NULL,
`search_keywords` text ...
- 681
1
vote
1
answer
349
views
MySQL memory usage overflow cause VM to reboot
I have 5 mysql servers in master-master group replication setup. (MySQL version 8.0.26)
2 nodes are having 1vCore and 2GB RAM and 20GB HDD configurations, and rest 3 nodes are having about 32GB RAM ...
- 111
5
votes
1
answer
1k
views
Optimize query matching first N items of an array
I'm working with a Postgres DB full of chess game data right now, where each game is a row in a 'records' table. The players' moves and the (optional) computer evaluations of those moves each have ...
- 53
2
votes
3
answers
990
views
Too frequent checkpointer
I got a few questions about checkpoint process and parameters for checkpointer.
My job is to optimize a large BI system. While testing and learning, I found articles about tuning base parameters. I ...
- 21
1
vote
1
answer
255
views
Optimize UPDATE query for single "next" row
We had information from new relic that one our query has a bad performance (almost 16 seconds for execution). We use PostgreSQL 11.8 on Debian Linux.
Table:
postgres=# SELECT table_name, column_name, ...
5
votes
1
answer
739
views
Query plan not reused for identical queries sp_executesql vs adhoc
One of the queries in my application is timing out. With the SQL Server profiler I was able to get the exact faulty SQL code. It's an sp_executesql dynamic statement that runs in > 2 minutes in ...
- 150
0
votes
3
answers
340
views
adding the right index to a table in a MySQL Database
We are having a big problem of high CPU usage of MySQL process in our database server, after investigation we have found that a big part of slow queries are related to a specific table and a big ...
- 123
8
votes
2
answers
2k
views
How can I achieve predicate pushdown in my view
I have a reporting table (about 1bn rows), and a tiny dimension table:
CREATE TABLE dbo.Sales_unpartitioned (
BusinessUnit int NOT NULL,
[Date] date NOT NULL,
SKU ...
- 9,095
1
vote
1
answer
86
views
Is there anything I can do to optimise this complex query?
I have a rather complex query that spans several layers of subqueries, the two primary ones fetching data from the exact same fields, only one takes the values themselves, the other takes aggregates.
...
- 151
6
votes
1
answer
401
views
Is there a more optimal way to create this view or stored procedure? Currently using a cross apply, but it's slow
I've updated this post with some test data.
I'm creating a report for my Movies database where I'd like for the end-user to be able to select movies of a certain genre. However, some movies have ...
- 61
0
votes
1
answer
83
views
Why doesn't MySQL optimizer do index merge from indexes in two different tables?
Query1 takes almost 2000x more time than Query2.
The criteria is of form
tbl1.key1 = x or tbl2.key1 = y
Query 1
SELECT
`t1`.`t1_PK`
FROM
`t1`
JOIN
`rel_t1_t2` ON `t1`.`t1_PK` = `...
- 207
3
votes
1
answer
24k
views
Mysql showing 100% CPU usage
We are facing problem of high cpu usage for mysql process (almost 100%). Here is the information related to server
Server Infos:
VPS - CENTOS 7.9 kvm - 6 GB RAM - 4 Core CPU - 180 GB SSD - MariaDB
And ...
0
votes
1
answer
390
views
How to run postgres in-memory on windows and sync to FS when a temer runs out?
So Progress is slow for me (in terms of query and update times) on SSD and it is ok in my case: I use it as a mix of Graph database + Table processor + NoSQL JSON trees so lots of loops, graphs, trees,...
- 75
0
votes
1
answer
47
views
Create Postgres Index for OR query
This is the query I want to optimize:
SELECT *
FROM invited_member_events
WHERE (user_id = '422820341791064085'
OR inviter_user_id = '422820341791064085')
AND guild_id = '...
- 113
0
votes
2
answers
1k
views
In PostgreSQL, how can I make Bitmap Index Scan parallelized?
I want to improve the performance of an SQL statement.
I am on version 13.
Here are the sample codes and the query I am interested in.
drop table ords;
CREATE TABLE ords (
ORD_ID INT NOT NULL,
CUST_ID ...
- 51
0
votes
1
answer
735
views
What is the proper configuration for max open connection, max idle connection and max connection life postgreSQL?
We have an app that serves small to moderate kinds of traffic.
our server spec:
6 node of micro/small aws ec2 for client
1 db.r5.2xlarge for writer
1 db.r5.large for reader
our app/client setting:
...
- 1
1
vote
1
answer
156
views
Manage billions lines of sensors data in mariaDB [closed]
I'm the new "IT guy" in a small company that managed ecological footprint reduction in industries, for contracts reasons they need to keep every data changes of each sensors(some changed ...
- 23
4
votes
1
answer
226
views
In PostgreSQL, what area in memory does HashSetOp use, work_mem or shared_buffer?
I want to know the difference between the hash table in a hash join and the hash table in HashSetOp Except.
Here is the PostgreSQL script I wrote to explain my question.
drop table if exists t1 ...
- 51
0
votes
1
answer
208
views
Do unused columns in MySQL/MariaDB notably affect performance?
In the WordPress MySQL/MariaDB database there is a table with some columns that in my own uses of WordPress are never used.
The exact columns I am talking about are:
comment_status varchar(20)
...
- 103
2
votes
1
answer
263
views
Postgres condition pushdown when using range conditions (>=, <= or BETWEEN)
I want to understand better the circumstances under which Postgres query executioner pushes conditions down
into the join components. I would be especially grateful for references to source code parts ...
- 121
0
votes
1
answer
235
views
Does postgres optimise repeated expressions?
select column_1, (column_1 <-> cube(:input)) / (:input <-> cube(0)) difference from table_1
for this query, is calculation of :input <-> cube(0) repeated for every row?
Also, for
...
- 183
2
votes
1
answer
774
views
MySQL performance issue when a hash join is used
I'm having an issue where the optimizer seems to be doing a full table scan and exhibiting a big performance hit when a hash join is used.
Schema and sample data:
CREATE TABLE `px_po_status` (
`...
0
votes
2
answers
1k
views
Cardinality , Parallel Hint alternative for MS-SQL
UPDATE MARK M SET ARCHIVE_FLAG = 'N' WHERE EXISTS
(SELECT /*+ cardinality(S1, 10) parallel(S1,8)*/ 1 FROM SHFASG S, SHIFT S1
WHERE S.ID = S1.ID AND M.ID = S.MARKID AND ARCHIVE_FLAG <> 'Y');
...
0
votes
2
answers
386
views
Optimizing nested SQLite query
I have a SQLite table named hashbands that looks like this:
hashband | file_id | window_id
------------------------------
potato | 0 | 0
potato | 1 | 0
The table has 100M+ rows (lol). ...
- 119
1
vote
2
answers
222
views
How to optimize a query with timestamp that using OR clause?
I have the follow table:
create table myTable
(
id serial not null
rowtype integer ...
- 137
2
votes
1
answer
108
views
MySQL performance issues with occasional complete freezes
we have a fairly large production DB currently at ~50GB but with constant growth. In the last couple of weeks, we are struggling with performances as some queries are getting very slow even if indexes ...
- 121
0
votes
1
answer
333
views
ola.hallengren - Alternative MySQL scripts!
Having worked with MS SQL for many years and found ola.hallengren scripts incredibly useful - I find my self in a MySQL environment.
Is there a MySQL alternative to ola.hallengren? - Perhaps someone ...
- 113
5
votes
2
answers
627
views
Why does SQL Server not perform constant (UNION ALL) branch elimination with OPTION(RECOMPILE) when selecting the result into a scalar variable?
We use some 'aggregate' views to select from multiple tables using a discriminator (note: these views are not partitioned views, because the discriminator is not in the base tables). This normally ...
- 195
1
vote
0
answers
320
views
Postgres Ignores Index When Ordering By Column From Joined Table
I am trying to figure out why Postgres is not using index when querying with ORDER BY clause with a SELECT query and 2 simple LEFT JOIN statements. I will explain my relevant database structure first:
...
- 111
1
vote
2
answers
1k
views
Best practice for "recycling" rows on PostgreSQL (UPDATE vs INSERT + DELETE)
I searched responses on "UPDATE vs INSERT + DELETE" in general terms and the consensus is UPDATE is faster, as expected.
My question is centered on a somewhat niche use case. Let's say you ...
- 111
-2
votes
1
answer
492
views
Do databases optimize random write and read operations? [closed]
Random write/read is slower than sequential for HDDs and some SSDs. Some database operations (UPDATE .. WHERE ..) may cause many random writes/reads. So I want to know if modern databases implement ...
- 233
0
votes
1
answer
500
views
Optimize Table query causing Fatal error during execution with InnoDB table
I have a problem with OPTIMIZE TABLE query with one of my InnoDB database tables.
I have a database that contains several different tables and data is constantly accumulating in these tables. However ...
- 11
0
votes
0
answers
109
views
Improving the run time of a DW query
I have a user running query which is sometimes under 10 seconds while other time going over 30 minutes.
This is not a store proc , he is doing it manually from his machine to get data and later after ...
- 774
1
vote
3
answers
2k
views
What is the best way to store large lists of user statistics?
I am trying to figure out the best way to store user statistics data in a table. For each user property, there can be anywhere from one to a couple thousand integer values (realistically, these would ...
- 121
0
votes
1
answer
397
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 ...
- 105
0
votes
1
answer
148
views
Mysql slow query on large table
With Mysql 5.6.10, I have a table like this:
CREATE TABLE `es_user_action` (
`id` bigint(11) unsigned NOT NULL AUTO_INCREMENT,
`user_id` bigint(11) unsigned NOT NULL,
`company_id` bigint(11) ...
- 101
1
vote
0
answers
94
views
MySQL pagination
MySQL - I am trying to fix this pagination issue on my servers . The RAM is 40 GB and the innodb_buffer_pool_size is set to 36 GB which is a little more than 80%. Still getting paging alerts. Any ...
- 11
2
votes
1
answer
2k
views
PostgreSQL IN query performance on a larger array
I'm fairly new to PostgreSQL and have a question about query performance.
I get a set of values by parsing a file and want to count how many of them already exist in a specific column in my database (...
- 23
0
votes
2
answers
114
views
How should I model my database - cannot decide on multiple options
I apologize if this might be a rather beginner-level-question.
For my database design, I have worked out multiple options but cannot decide on which one I should implement. I am using 10.4.17-MariaDB.
...
- 115
1
vote
1
answer
227
views
How to convince Oracle to create optimal execution plans in SQL queries with bind variables?
An attempt to parameterize a query in Oracle using bind variables and OR :B1 IS NULL or NVL(:B1,col1) in a WHERE clause leads to suboptimal execution plans.
For example, I'd like to write SELECT like ...
3
votes
1
answer
161
views
Ordering CONNECT BY siblings
I have an hierarchical query that uses CONNECT BY:
select
wonum,
parent,
classification,
classstructureid,
division,
worktype,
status,
glaccount,
fircode,
...
- 1,447
1
vote
1
answer
674
views
Mysql 5.7 innodb_buffer_pool_size - zero I/O?
I have a question regarding Mysql server 5.7, put the correct value for innodb_buffer_pool_size or increase RAM. Maybe I'm wrong, I'm not an expert, but let me explain what is it about.
Server ...
- 13
0
votes
2
answers
80
views
Does SQL Server optimize two ">" operations on same column
Can SQL Server optimize the following:
DECLARE @id INT;
DECLARE @d1 DATE;
SELECT a, b
FROM t
WHERE id_column = @id
AND date_column >= CAST(CURRENT_TIMESTAMP AS DATE)
AND date_column >= @d1
...
- 441
12
votes
1
answer
599
views
Why Does the SQL Server Query Optimizer Not Convert an OUTER JOIN to an INNER JOIN When a Trusted Foreign Key Is Present?
It appears that the SQL Server query optimizer does not translate an OUTER JOIN into an INNER JOIN when the join column in the first table is defined as NOT NULL and has a trusted foreign key ...
- 1,169
0
votes
1
answer
36
views
Optimizing simple query that performs a large table migration
There are two tables trains and train_statuses.
train_statuses already has a column train_status which is an ENUM.
CREATE TYPE train_status AS ENUM('queued', 'running', 'succeeded', 'failed', '...
- 131
0
votes
2
answers
189
views
Key Lookup isn't applied by default?
I'm trying to learn about covering indexes. In the Northwind database, I select from the table Categories:
As you can see the table has a non-clustered index on the column CategoryName.
This SQL ...
- 119
7
votes
2
answers
832
views
Indexed view not used in actual execution plan?
I've got a modest use case for an indexed view, which collapses out a column and sums all entries from a large table:
CREATE VIEW dbo.Losses_CombinedPortfolio WITH SCHEMABINDING
AS
SELECT [...
- 173
0
votes
2
answers
117
views
Asking Suggestions regarding mysql optimization
Im running a dedicated server with around 20-25 sites, almost all of them running wordpress installations.
Running it over a cpanel setup.
For a while its mostly mysql eating most of the cpu and ...
