Questions tagged [query-performance]
For questions about improving the performance and/or efficiency of database queries.
3,137
questions
-1
votes
1
answer
12
views
Optimize 5 million records SQL query (use join or not?!)
I have a table for products that contains almost 5 million records.
I have a column for the product category (product_category). That it is for now of type INT(11) and it is an index and refers to ...
0
votes
0
answers
14
views
What affects the fetch time and duration?
I am using mysql 5.7, and I am currently performing an inner join.
The first table contains about 10,000 names(only one column, with no index).
The second table contains the activity records with name ...
- 1
0
votes
1
answer
20
views
Partitioning vs Archiving Data for a specific use case
I'm not going to ask the difference between partitioning and archiving as there are countless of information out there on the net discussing them, and I have read the details of partitioning as ...
- 1
0
votes
1
answer
39
views
MySQL - Optimize select with join to a large table
I have done some digging and found exactly no help at all for questions similar to this one. Basically I have two tables where one stores user posts and the other holds the moderation of the records ...
- 101
0
votes
1
answer
38
views
How to reduce internal fragmentation while deleting rows in bulk
Context
I have a table in SQL Server with millions of rows and a primary key (by default, clustered key).
I got the requirement to do housekeeping for that table, so I need to delete rows in bulk ...
1
vote
3
answers
37
views
Is having a boolean column to store whether there are values for a multivalued attribute a bad micro-optimization?
This question is more theoretical. Let's say there is an entity in a one-to-many relationship. For example, a blog post can have zero or more tags. Those tags are stored in another table (that ...
- 23
11
votes
6
answers
4k
views
Order By causes a scan on a large table
I have the following query;
SELECT TOP 100 ID
FROM [dbo].[TableName] WITH (NOLOCK)
WHERE TypeId = 2
AND DateTimeUTC < '2022-Aug-04 07:02:40'
AND DateTimeUTC > '4/26/2022 7:36:36 AM'
...
- 121
1
vote
0
answers
34
views
speeding up mysql select's
I've a coldfusion application that currently contains just under 5,000 records on one table.
I've a query that selects around 3800 records from that table.
There are several queries that retrieve over ...
- 21
1
vote
2
answers
45
views
Getting the total size of rows with same ids across multiple tables restricted to a particular user id
I'm trying to calculate all the projects size of one user across multiple tables.
a unique project is separated into 4 tables with multiple rows assigned to it.
for the moment I'm only able to ...
- 133
4
votes
2
answers
104
views
Optimise query for fetching data across multiple days in a partitioned table
I'm trying to optimise a query to retrieve records across multiple days on a partitioned table (say: entries). The table is partitioned on a timestamp column (let's say: created_at) and for each day a ...
- 141
0
votes
0
answers
14
views
speeding up JOIN by LIKE or REGEXP
I have two tables of
CREATE TABLE titles
(
title_id int(11) unsigned NOT NULL AUTO_INCREMENT,
title varchar(255),
INDEX(title),
PRIMARY KEY(title_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE ...
- 4,261
0
votes
1
answer
16
views
faster INSERT INTO InnoDB table with composite PRIMARY KEY
I have a simple tag_map
CREATE TABLE tag_map
(
article_id int(11) unsigned NOT NULL,
tag_id int(11) unsigned NOT NULL,
INDEX(tag_id),
PRIMARY KEY(article_id,tag_id)
) ENGINE=InnoDB
The table is quite ...
- 4,261
1
vote
0
answers
16
views
Best way to create a View which includes counts for multiple one-many relationships?
Consider three entities - Forum, Thread & Topic, with the following relationships
Forum has many Threads
Forum has many Topics
I want to create a View forum_details that displays all columns of ...
- 185
0
votes
1
answer
28
views
Performance Problem : PL/SQL
SELECT
NULL AS TRX_NUMBER ,
NULL AS CUSTOMER_TRX_ID ,
VW_SAP_MPP_INV1.DOCENTRY AS DOCENTRY ,
VW_SAP_MPP_INV1.LINENUM AS LINENUM ,
VW_SAP_MPP_INV1.ITEMCODE AS ITEMCODE ,
VW_SAP_MPP_INV1....
0
votes
1
answer
17
views
'Query Performance plan' query in singlestore
when I go to memsqlOps > Explore > Query. I’m able to see query performance and active long running queries. What table or tables can I select from to get that same information?
- 242
2
votes
1
answer
42
views
Imporve MySQL InnoDB Full-text Search Performance
1.Background:
DB: MySQL 8.0, InnoDB engine;
Table size: About 2M rows of 2G data; FULL_TEXT index column: sentence (TEXT data type) https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html
Old ...
- 21
3
votes
0
answers
91
views
Execution Plan changes between good and bad query plans (Azure SQL Server)
I've done my best to follow Why is my query suddenly slower than it was yesterday?
The ShowPlanXML parses fine with SQL Sentry Plan Explorer but not via brentozar.com/pastetheplan (not sure why) so I ...
- 175
0
votes
1
answer
30
views
Query performance with integer field larger than value, partitioned by another column
Our postgres 9 Events table contains 1 Million records, comprising of 2 fields: Job and Task (job is varchar; task is integer)
A single Job might have multiple sequential Task(s), for example:
Job
...
- 101
1
vote
0
answers
34
views
Embeded query inside select : Postgres query optimizer vs other databases
I have a performance problem when I execute this query on PostgresDB :
SELECT u.login "Login",
CASE WHEN EXISTS (
SELECT 1
FROM journal_acces j
WHERE u.login = j.login)
...
- 119
3
votes
0
answers
48
views
Is there any way to optimize this query (generated by Drupal)?
I have an older system built on drupal, and there is a particular (generated) query that takes a long time - never completing in some cases.
The query:
SELECT
node.nid,
node.created,
node....
- 1,072
1
vote
1
answer
40
views
How do I SELECT elements using WHERE with another table's record values in WHERE statement?
Problem summary
Today part of my app crashed, but nothing else stopped with it. I need to find the first and last tracked message ids for each channel during the downtime. I log unix timestamps ...
- 13
0
votes
2
answers
36
views
Query optimization [Oracle DBMS]
I have a query that always must return a single row and its execution time is about 5s, so I need to optimize it:
SELECT * FROM RESPONSE res
INNER JOIN REQUEST req ON res.ID = req.ID
WHERE res....
- 1
0
votes
1
answer
24
views
Can't get this GROUP BY or DISTINCT right
On MySQL 5.7.34 I have this simple db structure. Important columns are domain name, domain total links in, but entries are duplicate (once for each found link from various sources), I need to select ...
- 133
0
votes
1
answer
59
views
Filtering a recursive CTE as view
i'm using MSSQL Server 2017 and have a CTE used in a view to get the root element of a hirarchical tree linked to one specific element.
If the CTE is used directly and the base elements are allready ...
- 3
0
votes
1
answer
33
views
Can MySQL use a where clause to inform a join?
For context, I'm using an ORM (ActiveRecord for Ruby On Rails), but having to dip down into MySQL to run some rather complex queries. As a result, it's more difficult to do some things than it would ...
- 103
0
votes
1
answer
20
views
How to create a table with an index that allows me to order by the time from now?
I have a table with a date time column. I have a query currently written as:
SELECT *
FROM foo
WHERE is_relevant = true
ORDER BY ABS(TIMESTAMPDIFF(SECOND, `foo`.`date`, NOW()))
The goal is to return ...
- 103
0
votes
2
answers
24
views
MongoDB - query performance when sharding by _id
When we shard a collection by the MongoDB's _id field, we could achieve the most uniform distribution there is but the docs indicate query performance doesn't scale well due to the scatter gather ...
- 117
0
votes
1
answer
19
views
Better ways of concating the value of columns
I have a table with below structure and sample data:
create table CUSTOMER_TEST
(
customer_num Number,
rel_one Number,
rel_two Number,
rel_three Number,
rel_four Number
)
...
- 1,304
1
vote
1
answer
49
views
Is a correlated function in the FROM clause executed for every row? [closed]
I have a heavy function, let's call it fcalc(x,y) -> my_z. I need the result my_z to both be a filter (too low and the row is discarded) and in the result set (so my client can see it). I write the ...
- 13
0
votes
2
answers
31
views
Product alternatives query performance slow - Many to many tables and ROW_NUM(OVER PARTITION...) optimisation needed?
When the user selects a product in my web app, I run a query to search for related alternatives based on product categories linked to the product via a many to many relationship with a category ...
- 101
0
votes
1
answer
38
views
Postgres query with large IN, and joining on a temporary table doesn't seem to work
EDIT: The query plans in the question body come from EXPLAIN, but as @jjanes suggested, EXPLAIN (ANALYZE, BUFFERS) may be more useful. Since the output is very large I have uploaded them here: https://...
- 103
0
votes
0
answers
62
views
Improve a query parameter sniffing scenario
I have to admit I am a computer programmer, and not a DBA so I lack a few prerequisites to do this analysis work properly.
That said, I have optimized an ORM generated query, and now the performance ...
- 287
3
votes
1
answer
183
views
MariaDB Query Optimizer ignores index
MariaDB Version 10.5.15
simple Table, two columns with an index
If I make a query on a table and use two columns in the where conndition that have an index, no index is used. Why?
CREATE TABLE `logs` ...
- 133
5
votes
2
answers
216
views
Is this DELETE ... WHERE EXISTS query plan O(n*2) or O(n^2)?
I'm trying to perform a common task, deleting duplicates from a table with the aim of adding a unique constraint.
CREATE TABLE IF NOT EXISTS item_identifier (
pk BIGSERIAL PRIMARY KEY,
...
- 1,489
0
votes
1
answer
109
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,...
- 145
2
votes
1
answer
76
views
EXCLUDE CURRENT ROW cripples performance of window functions
I'm working with a customer's database where there are rows representing instances of Users, Activities (table named ActivityRecords), and ActivityUsers in correspondingly named tables, where ...
- 23
0
votes
1
answer
32
views
any suspicious result in that explain analyze?
I have the following query :
SELECT "message"."id" AS "message_id", "message"."conversation_id" AS "message_conversation_id"
FROM "...
- 101
-1
votes
1
answer
127
views
SQL Server stored procedure taking too long sometimes even though indexes have been added with memory grant issues
I have a stored procedure that takes too long sometimes even though I have added all suggested indexes.
I keep getting the Query had to wait for memory grant
The query plan can be found here: Query ...
0
votes
0
answers
27
views
Filtering timestamp record by range
So i am new at optimizing SQL and here is my query:
COUNT (SI_2_STRIPPED.RESOURCE_UUID) AS COMPLETIONS
FROM
(SELECT SI2.STATISTIC_ID,
SS.RESOURCE_UUID
FROM ...
- 1
0
votes
2
answers
23
views
Optimizing querying for any of multiple terms in string
I have been pondering what would be the most efficient way to search for any of multiple urls contained in an unstructured text field.
Suppose I want to find posts containing either https://...
- 117
0
votes
1
answer
30
views
How can I optimise this MySQL query?
I'm trying to make a filter which matches every record in the table to find ones which are within the range @min and @max. They're unxi timestamps if anyone was wondering.
It does work right now, but, ...
- 13
2
votes
1
answer
42
views
MySQL Query slows down on concurrent requests to different subpartitions of a partitioned table
We have partitioned a large table (with over 100 million records) into multiple partitions and subpartitions in our Serverless Aurora to query different subpartitions in parallel. But we find that ...
- 121
2
votes
1
answer
47
views
How to improve simple = condition on a large table?
Check out the following simple query:
SELECT * FROM "teammgr_team" WHERE ("teammgr_team"."real" = true AND "teammgr_team"."name" = 'abc');
But it ...
- 133
2
votes
1
answer
41
views
How to efficiently run range based queries in Cassandra
I wanted to use Cassandra in a project, but it's important that I'm able to do a few ranged queries (for example, 12345 <= time < 67890 ).
Unfortunately, Cassandra's design seems to preclude ...
- 147
2
votes
0
answers
67
views
Service Broker performance issues after upgrading from 2012 to 2017 - tons of LCK_M_IX
Last night we upgraded a SQL Server 2012 server to SQL Server 2017 + CU 29. This is one of a matched set - we send the same message to both and each processes it. No, this is not AlwaysOn; we're ...
- 433
0
votes
1
answer
29
views
Best approach for basic PHP DB session data storage
I've decided to switch from PHP's native session management based on $_SESSION to use my own, due to many different issues I've had so far with PHP's possibilities, and also because I'm building an ...
- 149
0
votes
0
answers
60
views
MySQL reading speed is much slower on a more powerful server
I have recently provisioned two new servers:
Server 1: AMD EPYC 7320P, 128GB DDR4, 7.68TB Micron 5300 SSD
Server 2: Intel E2388G, 128GB DDR4, 2x 6TB SATA HDD in software RAID1
Both are running ...
- 101
1
vote
0
answers
32
views
Slow Query Log Only Logs Few Queries
Slow query is enabled but only logs few queries when you retrieve the slow log. It’s been working ok all last week, suddenly this week, it only logs just 2 or 3 queries.
Long query time = 0.01 and I ...
- 11
0
votes
1
answer
76
views
MySQL - Optimizing a large table's join
Im using the following query. This eat my complete CPU in Aurora Cluster. Also too much of wait. I tried by tunning index, but still no luck. Is there any recommendation to make it perform better?
...
- 1,708
0
votes
1
answer
31
views
Slow query optimize help
Db is on Aurora cluster. Added indexes. removed functions from where clause. rewrote some logic. Got number of records to measurable limit, but still the query is performing slow. Attaching query plan ...
- 1