Questions tagged [query-performance]

For questions about improving the performance and/or efficiency of database queries.

Filter by
Sorted by
Tagged with
-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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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' ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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....
user avatar
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?
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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) ...
user avatar
  • 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....
user avatar
  • 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 ...
user avatar
  • 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....
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ) ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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://...
user avatar
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 ...
user avatar
  • 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` ...
user avatar
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, ...
user avatar
  • 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,...
user avatar
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 ...
user avatar
  • 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 "...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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://...
user avatar
  • 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, ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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? ...
user avatar
  • 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 ...
user avatar
  • 1

1
2 3 4 5
63