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,964
questions
1
vote
1
answer
62
views
Using table valued function as a table in sql server join
I have a scenario where SQL server using table valued function with joins to many other tables. The function returns many columns, which are not always desirable output for every report, causing lots ...
- 11.7k
0
votes
2
answers
76
views
formating date colums causing performance issues
I have a query(written by my previous co-worker) with date columns returned as output but formatted, which s causing performance degradation (for below query returning 10k records, performance ...
- 35.1k
0
votes
2
answers
4k
views
MySQL crashes and currently the error.log file is huge
I've created a droplet on DigitalOcean created using Laravel Forge 6 months ago. Two weeks ago, we decided it was time to upgrade the droplet and we moved from a 4GB RAM/2CPUs to a 16GB RAM/6CPUs ...
CommunityBot
- 1
0
votes
0
answers
22
views
resume search with full text index
I have a database structure where resume stored in document column in WHDocument table in database for each person. During skilled person search in application the persons skill is also searched for ...
- 25
1
vote
2
answers
44
views
Is there a "correct" way for an optimizer to process a subquery if the outer query has additional where filters?
This topic came up for me as the result of investigating an error in Redshift. We have a table containing a column of numbers represented as text, but for rows where no value is present a single ...
- 1,480
0
votes
1
answer
538
views
ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes
An online optimization tool offered to add indexes. One of the index is successfully created but i cant create the second one.
Do you think that indexes going to reduce query run time?
Any ideas how ...
CommunityBot
- 1
0
votes
1
answer
1k
views
Query with left join not using index
The below query is taking about 1 second to run
SELECT `taxi_driver_mapping`.*,
concat("dpk00",
taxi_driver_mapping.mapping_payment_plan_id) AS package_text,
`people`.*,
...
CommunityBot
- 1
2
votes
1
answer
797
views
How to count selectivity rows in PostgreSQL 8.2
I have problem with two queries on PostgreSQL v8.2. It seem like the optimizer chooses a wrong index:
db=# explain
select count(*), messagetype, alias
from event
where
templateinfoid = 10 and ...
CommunityBot
- 1
2
votes
2
answers
1k
views
Nested loop on primary key
CREATE TABLE `request` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`created_by` int(11) unsigned DEFAULT NULL,
`content` text,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
...
CommunityBot
- 1
1
vote
1
answer
577
views
Simple query with a single join very slow
I have this very slow, simple query that joins a large table (~180M rows) with a smaller table (~60k rows) with a foreign key, filtering an indexed column on the smaller table, ordering by the primary ...
CommunityBot
- 1
0
votes
3
answers
3k
views
mysql simple update with 40 million and 128GB RAM taking too much time
We are having trouble with simple updates on a single table taking a long time. The table contains ~40 Million rows.
and the job runs every day that truncates the table and inserts new data from ...
CommunityBot
- 1
0
votes
1
answer
41
views
Optimizing the SQLite database as source data for R scripts
I have an application written in R Shiny that supports a large number of csv's (and 3 new ones are added every day). These files have a structure that is quite unpleasant to handle, so I decided to ...
- 35.1k
-1
votes
0
answers
63
views
Stored Procedure execution became very slow all of a sudden in SQL Server 2012
I have a stored procedure that gets executed over 100,000 times per hour, with each execution typically taking less than 1 millisecond. However, there was a sudden occurrence where it started taking 1 ...
- 301
6
votes
1
answer
397
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 ...
CommunityBot
- 1
1
vote
1
answer
45
views
MySQL - Slow Performing Subquery when DISTINCT Is Used
I have this query that runs very slowly when DISTINCT is used;
SELECT COUNT(*) FROM (SELECT DISTINCT query_text FROM search_query WHERE (store_id IN (1)) AND (num_results > 0) ORDER BY popularity ...
- 3,780
0
votes
2
answers
368
views
How to improve query count execution with mySql replicate?
For my report I have to use count queries (name 'user_activity' for example) and then group by by another column (user_type) in another table (user table).
And its really time consuming when it ...
CommunityBot
- 1
3
votes
1
answer
3k
views
How to optimize a Full Text Search
So we have this VARCHAR(255) column called code, which is a string of varying size we use in a WHERE clause.
Doing this search slows the query down by about 300ms and since the query is run a few ...
CommunityBot
- 1
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';
+-----...
CommunityBot
- 1
0
votes
2
answers
379
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 ...
CommunityBot
- 1
0
votes
1
answer
658
views
How to optimize UPDATE with a nested SELECT subquery?
I wrote a complicated UPDATE query, and it works, but it looks menacing. Here's what I'm trying to do:
In each topic user 'Bob123' posted anonymously. When you post anonymously in a topic, you get ...
CommunityBot
- 1
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 ...
CommunityBot
- 1
0
votes
2
answers
881
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 ...
CommunityBot
- 1
2
votes
2
answers
1k
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 ...
CommunityBot
- 1
0
votes
1
answer
395
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 ...
CommunityBot
- 1
0
votes
2
answers
387
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 ...
CommunityBot
- 1
0
votes
1
answer
45
views
SQL Server - Update Statistics FULL scan vs Auto Update [duplicate]
Something I have been trying to figure out..
I take a table with 2 billion + rows and I run a manual stats update with FULLSCAN
Sometime in the future, an automatic update stats runs against the table,...
- 516
3
votes
1
answer
774
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
...
CommunityBot
- 1
1
vote
1
answer
38
views
Wrong execution plan in MySQL 8.0 when using JOIN in WHERE NOT IN clause
After a migration from MySQL 5.7 to MySQL 8.0.34, we have a very strange behavior with a query when semijoin is on in the optimizer_switch.
1. The problematic query:
SELECT COUNT(s0_.id)
FROM ...
- 76.9k
1
vote
2
answers
583
views
How to optimize a query on a large table, besides using index?
I need suggestions/ideas of optimizing the following query:
SELECT COUNT(*)
FROM (SELECT 1
FROM f
WHERE parent_sha256 = $1
AND parent_sha256 <> sha256
LIMIT 1) AS ...
- 47.8k
1
vote
1
answer
86
views
SQL 2019 - BatchModeOnRowStoreUsed - Legacy Cardinality - IQP
Have recently migrated from SQL 2016 where the DB's compatibility level was all set to 2008 to SQL 2019 where the compatibility level for all DB's are now 150.
Upon running workload for a while, I ...
- 38.8k
0
votes
0
answers
36
views
Optimizing SELECT COUNT(DISTINCT) on a table increasing daily
Let's say we have a table Daily_users which has the columns student_id, school_id, grade, timestamp. We collect usage data of students daily and so the table grows daily (note that there could be ...
- 101
0
votes
2
answers
81
views
How to choose index to optimize this MySQL query?
SELECT * FROM ehd_historical_data
WHERE exchange = 'NYSE ARCA' AND symbol = 'ZROZ' AND blacklisted = 0
ORDER BY high DESC LIMIT 1;
SELECT * FROM ehd_historical_data
WHERE exchange = 'NYSE ARCA' ...
- 76.9k
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 ...
CommunityBot
- 1
1
vote
1
answer
759
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 ...
CommunityBot
- 1
1
vote
1
answer
787
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 ...
CommunityBot
- 1
2
votes
2
answers
905
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 ...
CommunityBot
- 1
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 ...
CommunityBot
- 1
1
vote
2
answers
100
views
Why can SQL Server not perform a TOP N SORT between an Index Seek and the Key Lookup?
I am looking into a minor performance issue, where an optimizer tool is basically saying, "hey just go ahead and include all the columns on this table in this index" which is a horrible ...
- 82.9k
0
votes
2
answers
3k
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:
...
CommunityBot
- 1
1
vote
2
answers
651
views
CPU High usage crashing our server
The server where my database is running is suffering from CPU spikes. We're having trouble identifying what is causing these CPU spikes, and consequently how to mitigate them.
I've tried adding some ...
CommunityBot
- 1
1
vote
2
answers
643
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 ...
CommunityBot
- 1
2
votes
3
answers
976
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 ...
CommunityBot
- 1
1
vote
2
answers
298
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, ...
CommunityBot
- 1
1
vote
1
answer
35
views
Finding the latest status
I have 2 tables events and event_status.
The event_status table is the history table, and latest being the current one. The events and event_status have a one-to-many mapping.
I need a optimized query ...
- 76.9k
0
votes
2
answers
468
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 ...
CommunityBot
- 1
3
votes
1
answer
842
views
How can I find out if a SQL function can be inlined?
In this answer Erwin breaks down IMMUTABLE functions into those that can be inlined, and those that can't. He has an example with to_char() that shows an IMMUTABLE function over to_char() will ...
- 172k
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 ...
CommunityBot
- 1
4
votes
2
answers
112
views
Seeking Help Optimizing Self-Joins
This query takes ~5x longer than it should due to the WHERE IN() statement at the end. Is there a way to check IN() the resultset of the outer query?
SELECT SC.ROW_ID C1_CASE_ID,
SC2....
- 41
0
votes
1
answer
63
views
Building and Executing a Query Execution Plan Manually in PostgreSQL
An SQL query goes through multiple stages to be executed. This is a wide topic, but in summary, multiple execution strategies are proposed for each query, they are evaluated, and one is selected for ...
- 103
0
votes
1
answer
846
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 ...
CommunityBot
- 1