Questions tagged [query-performance]
For questions about improving the performance and/or efficiency of database queries.
2,898
questions
1
vote
1
answer
535
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.....
CommunityBot
- 1
1
vote
0
answers
36
views
PostgreSQL 14 blocked
I have a fairly serious problem with PostgreSQL, it happens at intervals of a few days that some tables on my PostgreSQL instance crash, or rather this is my feeling.
Linked queries are very slow.
...
- 11
0
votes
2
answers
46
views
Improving stats for a join to avoid Nested Loops with LIMIT
I have a main fact table t1 with ~6.9m rows, a second dimension table t2 with 1329 rows, and a third table t3 with ~6.9m rows (1-1 with t1, separate because it's produced by a different batch process)....
- 36.4k
0
votes
0
answers
27
views
Trying to optimize an aggregrate query for a big table in postgres
I'm trying to retrieve to get a top 3 of players from a big database containing their stats.
To do this i want to sum the kills they got in all of their games and select the 3 that have the most kills....
0
votes
1
answer
62
views
SQL to check if a certain time duration occurs within a particular period of the day
I'm working with data that has a start datetime and end datetime column for each event which can be a short period of time
(Few hours) up to a number of days in duration.
I'm trying to establish a way ...
- 421
0
votes
1
answer
48
views
Can anyone please suggest the things which are bad with this SQL query
I saw this query somewhere as a bad example which took their application down.
select oldurl,
newurl,
id,
dateadd
from jos_sh404sef_urls
where newurl <> ""
...
- 31.8k
1
vote
1
answer
839
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
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 ...
CommunityBot
- 1
1
vote
2
answers
55
views
Simple query with two WHERE clauses uses inferior index scan
I have a table named "db_log" with a size of 11GB and approximately 10 million rows in PostgreSQL 13.7 version. The issue is that a simple query is not performing an full index scan for ...
- 74.8k
0
votes
1
answer
582
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 ...
CommunityBot
- 1
0
votes
1
answer
45
views
When using a CTE, why do I need to use SELECT twice in order to filter on values from an auxiliary statement?
WITH aux_results AS (
SELECT id
FROM vendors
)
SELECT c.id
FROM customers AS c
WHERE c.vendor IN (SELECT * FROM aux_results);
Here is a naive and simple example of a CTE. The ...
- 167k
1
vote
4
answers
71
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
629
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 ...
CommunityBot
- 1
3
votes
1
answer
2k
views
SELECT Query is slow when run remotely in MySQL
When I connect MySQL from remote server and run below query then the results are very slow(Sometime even faced lost connection Issue). However when I run the same SQL query on the local machine then ...
CommunityBot
- 1
0
votes
1
answer
254
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 ...
CommunityBot
- 1
0
votes
2
answers
279
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 ...
CommunityBot
- 1
0
votes
0
answers
15
views
Memory of server never drops data of sql server [duplicate]
I have this problem that occurred in one of the servers our System works in
the SQL server consumption of memory grows and grows until it gets 96% of memory
the system makes a request to the database ...
0
votes
3
answers
234
views
Performance issues after upgrading from MySQL 5.7 to MySQL 8.0
Recently I upgraded MySQL on my server from newest 5.7.x to newest 8.0.x and I imported my entire database with multi-indexes that I created when I was using MySQL 5.7.x. These indexes optimize SELECT ...
- 74.8k
1
vote
1
answer
2k
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,...
CommunityBot
- 1
0
votes
0
answers
21
views
Architecture of tracking the most recent date a decimal became negative
I have a column X which is a decimal data type. I would like to track the last date that X became negative. For example, X goes from 10 to -10 on Jan.1 and then to -20 on Jan.2. The last date X ...
- 121
0
votes
1
answer
438
views
Optimizing big sorted join (nested loop) based on value frequency
I have a query involving two tables, let's call them timeline and events, defined as follows:
create table timeline (
event_id int,
ts timestamptz
);
create table events (
id int primary key,...
CommunityBot
- 1
1
vote
1
answer
472
views
What is the most performant way to group by and join high cardinality columns in column based table?
I currently have a SQL Server, 20 column fact table that could grow even wider into the hundreds. The current row count is about 9 million records. The indexing strategy is currently row-based. The ...
CommunityBot
- 1
3
votes
4
answers
276
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 ...
CommunityBot
- 1
2
votes
1
answer
639
views
MariaDB: Querying a timestamp partition
I'm having difficulties getting my queries to make use of the partitions when I'm selecting. They need to be able to do > sometime > sometime and between sometime and anothertime. However, if I ...
CommunityBot
- 1
0
votes
2
answers
258
views
Performance of implementing product images in an e-commerce store as a separate table vs. as columns of the Product table?
I am creating an e-commerce store in Django and am currently trying to decide between implementing product images as an entity of their own then linking to the product by a foreign key or putting ...
CommunityBot
- 1
12
votes
2
answers
683
views
Unable to execute query and not even able to generate Estimated execution plan
I am working on SQL Server 2019.
I have a table dbo.AllDates where I have all dates from 1990 to 2050. I have another table dbo.ActualExchangeRates where I have actual exchange rates for certain ...
- 80.3k
3
votes
2
answers
140
views
Impact of index on a "status" field with one (guaranteed) change
Introduction
I have a PostgreSQL table setup as a queue/event-source.
I would very much like to keep the "order" of the events (even after the queue item has been processed) as a source for ...
- 167k
1
vote
1
answer
512
views
How to make delete duplicates faster?
On a mysql table with about 1.7M rows, I tried to delete duplicates posts:
delete a FROM comment a
INNER JOIN comment a2
WHERE a.id < a2.id
AND a.body = a2.body;
The result was:
...
CommunityBot
- 1
1
vote
1
answer
392
views
Why turning off condition_fanout_filter speed up query
bin/mysqld_safe --user=mysql --optimizer-switch=condition_fanout_filter=off --innodb-flush-method=O_DIRECT &
Query (from TPC-H query-19):
SELECT Sum(l_extendedprice * ( 1 - l_discount )) AS ...
CommunityBot
- 1
1
vote
2
answers
37
views
MySQL - Create a Covering Index where the Sorting Option Columns are Identical to the Select Payload Columns
Something to note up front, I am using Magento 2 so if the query looks a little strange, that's why.
I have the following query and am trying to optimize the indexes for the table;
SELECT `main_table`....
- 79
1
vote
2
answers
91
views
Speeding up Select Query
I have the following query which needs to run up to 40-50 times on some pages.
SELECT lid, dest, rebuild FROM {store_links_link}
WHERE source = :url;
Source column is URLs and the problem is that ...
- 79
0
votes
1
answer
236
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 ...
CommunityBot
- 1
1
vote
1
answer
241
views
Best way to speed up a linked server query - 49 columns plus two calculated columns and 170 mill rows
I am trying to speed up at linked server query. The source table has 170 mill rows, and we need to retrieve all of them.
Trying to insert this query in a table on destination server takes over 2 ...
CommunityBot
- 1
0
votes
2
answers
678
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 ...
CommunityBot
- 1
1
vote
1
answer
70
views
Worst performance multiple left join big tables after restore DB
Problem
Database: PostgreSQL 13.8 (Debian 13.8-0+deb11u1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
After migrate from DigitalOcean to strong Dedicated Servers ...
- 167k
1
vote
1
answer
53
views
MySQL explain plan doesn't show key lookup
I have a query that's really slow when I select a lot of columns but greatly speeds up when I only select the columns present in the index the query is using. This makes me believe that the query is ...
- 165
0
votes
1
answer
58
views
SARG-able date conversion
I just came across some SQL Server queries that have the following non-SARG-able predicate in their WHERE clauses:
AND ((CONVERT(DATETIME, SWITCHOFFSET(T.TimeStamp, DATEPART(TZOFFSET, T.TimeStamp AT ...
- 9,920
1
vote
1
answer
231
views
Query sometime not using index
Starting in the middle of last night (of course) I have a query that stops using an index and when that happens, it takes over an hour to complete vs. about 3 seconds when it uses the index. This ...
CommunityBot
- 1
0
votes
1
answer
805
views
Disk queue length very high in SQL Server 2016
I have two Servers A and B with the same settings, but on Server A, the query runs in 6 minutes and on Server B the same query runs in 15 minutes.
what I noticed is that on Server B is that disk queue ...
CommunityBot
- 1
1
vote
0
answers
44
views
Compile time of update query is high on sqlserver 2019 vs sqlserver 2016
A dev team announced performance degradation after migrating their database from SQLServer 2016 to SQLServer 2019. So I started tracing the database using SQL profiler.
It turns out duration, cpu ...
- 9,920
1
vote
1
answer
480
views
join_buffer_size causes high cpu load
I am playing and testing with my.cnf and at the moment I have these server specs:
12 CPU vCore
500 GB SSD
24 GB RAM
1 GB Swap
and this is the input of my my.cnf:
# GENERAL #
# user ...
CommunityBot
- 1
2
votes
1
answer
733
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
1
vote
1
answer
562
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: '...
CommunityBot
- 1
0
votes
2
answers
1k
views
Slow query compilation, lots of reads during compilation phase
I have a query of the following form:
exec sp_executesql N'
UPDATE table
SET column1 = 1, modify_date=N''2020-02-12 04:55:59.000''
WHERE (column5=@P1
AND column6=@P2)',
N'@P1 ...
- 80.3k
0
votes
2
answers
59
views
What's the most performant condition when dealing with multiple OR?
Let's assume a translation table:
Column
Type
🔑 id
bigint
key
text
locale
varchar(10)
value
text
with a unique index on (key, locale).
I have a bulk endpoint where I want to get multiple key/...
- 31.4k
2
votes
0
answers
54
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 ...
- 167k
1
vote
2
answers
63
views
How to interpret the stats of index usage? [closed]
Question: How do we interpret the stats shown in the diagram below? The stats seem to indicate bad index designing, how can it be improved. I realize this a sort of broad question since it depends on ...
- 16.5k
3
votes
2
answers
304
views
How to gather Index usage information
Following query correctly outputs number of seeks, scans, etc. I have question on understanding some rows in the output. In some output rows of the query, the index name is shown as NULL and the ...
- 37.5k
-1
votes
1
answer
25
views
Ignoring Index, default to sorting issue [closed]
SELECT
count(
DISTINCT concat(
country,
col1,
fcol1,
mcol1,
col2,
fcol2,
mcol2,
col3,
...
- 7,854
2
votes
2
answers
342
views
Slow query for multiple data at same timestamp
Hi everyone,
I'm currently facing a problem to query efficiently my database to retrieve all rows at same timestamp.
I will explain,
I have two table of data using TimescaleDb Hypertable with ...
CommunityBot
- 1