Questions tagged [optimization]

In the context of a database, optimisation refers to the process of the query optimiser selecting an efficient physical execution plan.

Filter by
Sorted by
Tagged with
1
vote
1answer
24 views

How to Speed Up my SQL Query?

I have a query that is taking much longer than I expect it should, and I can not figure out how to speed it up. For a little bit of background on the table structure, these are audited records. There'...
0
votes
0answers
14 views

Optimize slow COUNT in MySQL subquery

I have the following query that takes minutes to complete: SELECT DISTINCT Designation, Model, ( SELECT COUNT( Equipment.EquipmentID ) FROM Equipment INNER JOIN EquipmentDesignation_Vw ...
1
vote
1answer
16 views

PostgreSQL not using the index on queries that return large number of records

I have a table called my_table which contains about 30M records. The table has an index set on column_a. However, I'm noticing that when number of returned queries are high, the index is not used. ...
0
votes
1answer
13 views

What exactly happens when optimizer_prune_level = 1?

This documentation link states that optimizer_prune_level Controls the heuristics applied during query optimization to prune less-promising partial plans from the optimizer search space. Can ...
3
votes
1answer
60 views

Constant Scan joining

In preparation of my previous Constant Scan question I was experimenting with VALUES in various ways and came across the thing regarding joining of VALUES that looks odd to me. Setup is simple ...
0
votes
1answer
43 views

How to make PG query faster even when it is using indices

I have a PG table with about 30M records. I'm noticing that when I query the table with IN clause that would fetch lot of rows, the query is taking a long time to respond (~3 minutes) Question ...
0
votes
2answers
30 views

usage percentage of Mongodb internal cache

I've ran two performance test on a MongoDB server with identical environment/settings AFAIK. I found that the throughput was 10% apart. When I inspected the mongostat logs, I found that Mongodb in ...
0
votes
0answers
41 views

many of the same queries on the query plan - whats the pros and cons of Forced Parameterization in this scenario?

while working on finding all the queries in the query plan that are related to a particular index I realised that there were too many duplicates of the same queries on the query plan. Finding what ...
1
vote
1answer
21 views

Add new columns on existing or auxiliary table?

I have a database with the following table: stores - ID - name - phone number - email address After adding around 4000 stores to the database we figured that we will need to allow store owners to ...
1
vote
1answer
41 views

Postgres query plan shows sequential scan instead of a index scan with on the fly data

My use case is I am trying to select rows based on a list of known text values. For example, I have known text values as 'Frasier' and 'Cheers' and I want to select some rows that match the values. ...
2
votes
0answers
50 views

Optimizing a paginated query with various filters and dynamic sorting

Introduction Imagine a (fairly advanced) ToDo app in which items are catogorized into lists (which can be temporarily deactivated), have custom additional attributes, can be assigned to a user and ...
0
votes
0answers
14 views

Comparing Across Tables to Find Most Recently Modified Row

all. Ive been working with some Sales Force data to solve some data synchronization problems we have between two instances of SFDC. Quick Background We have two instances of SFDC (SalesForce) that ...
1
vote
0answers
14 views

Not able to update innodb_ft_num_word_optimize

We are using a AWS-RDS(MySQL 5.7) instance with 16GB RAM. We have a table with millions of entries and a FULL-TEXT index which needs to be OPTIMIZED. Apparently, FULL-TEXT optimization is very ...
-1
votes
1answer
28 views

What is the best DB schema to handle exceptions and prevent redundancy?

There are some products that can be sold in multiple units and the base unit is kilogram. The unit of each product has different weight, for example a Bundle of product A is 20kg but a Bundle of ...
3
votes
3answers
143 views

Getting Index Scan instead of a possible Index Seek?

Currently learning a few things on query optimization, and I've been trying out different queries and stumbled upon this "issue". I am using the AdventureWorks2014 database, I ran this simple query: ...
0
votes
2answers
38 views

Storing the groupwise most recent related record

I have two tables, customers and purchases. There are a lot (thousands) of purchases per customer. I usually only need the most recent purchase for each customer, which is why I have the "...
0
votes
1answer
50 views

How to reduce the response time of a query that yields ~4.9 million rows in ~12-13 seconds

I have the following doubt about the execution performance of a query. This query takes about 12-13 seconds, and brings a total of 4,879,049 rows. Could I lessen this query's response time? The query ...
4
votes
2answers
101 views

How to optimize IN query on indexed column

I have a table with over 50M records. One of the fields is COLOR_CODE. I've set an index on the column COLOR_CODE like this: "mytable_colorcode_idx" btree (color_code) I'm noticing that when I run ...
0
votes
0answers
44 views

Do i need to add index for apply filtering for this query?

Below is my query: with cte as (SELECT a.postId from (SELECT p.postId FROM join_area ja INNER JOIN post p USING (areaId) WHERE ja.belongAreaId = 29 AND p....
-3
votes
1answer
44 views

What actions or practices contribute to high CPU and RAM usage in SQL databases? [closed]

I'm curious about which common practices or actions contribute to spikes or high CPU usage and RAM usage for SQL databases, I am trying to estimate the resources I would allocate to a new MySQL ...
-2
votes
1answer
35 views

Performance using a jsonb column in PostgreSQL? [closed]

The current schema at my company uses jsonb columns heavily (which get queried on) which although works, I have heard is not good design. Someone told me querying on a field in a jsonb column would ...
1
vote
1answer
17 views

How to skip prefix column check in multi column index in MySQL?

Below is my demo table structure: CREATE TABLE `table1` ( `postType` tinyint(4) NOT NULL DEFAULT '0' COMMENT '0-for rent, 1-to rent', `postLocation` smallint(6) NOT NULL, `postArea` smallint(6) ...
0
votes
0answers
79 views

SQL Server Query Timeout with NOT IN clause but IN works fine

So were using SQL Server 2016 Enterprise and suddenly have an issue with a query on the Production server. Works fine on Test and Dev and if we backup and restore the production DB to either of ...
1
vote
1answer
95 views

Query performance of index scans slower than parallel seq scan on Postgres

I've been trying to debug a performance problem on PostgreSQL 10 on AWS Aurora. Specifically I'm trying to understand performance problems on "cold" queries where the buffer cache does not contain ...
4
votes
1answer
88 views

OPTIMIZE FOR variable error

I'm having a problem specifying a value within an OPTIMIZE FOR statement. I'd like to optimize the query with the value as a string, but I must be doing something wrong, because SQL gives the ...
1
vote
1answer
37 views

MySQL order of execution

I am running a very long query described below. It fetches the next needed action for each account on an automation system. I.e. SELECT Account.id, (IFNULL(**Should send message query**, ...
6
votes
0answers
91 views

Can oscillating number of fragment decrease FTS query performance?

I've got a FTS Catalog on a table containing ~106 row. It used to work like a charm, but lately, for an unknown reason it started to get very bad (query >30 sec) performances randomly. Reading ...
1
vote
1answer
27 views

Predict how much hard drive space a query might cost?

Once in a while I run into "No space left on the machine" after running a lengthy query. I also like to have a reasonable estimation, so that the IT team can purchase a correct cloud server. My ...
0
votes
0answers
21 views

How can i remove filesort for this subqueires?

This is the Query: SELECT A.* FROM messages_thread_user mtu, LATERAL (SELECT m.*, u.userName FROM messages m JOIN ...
1
vote
1answer
41 views

Optimize Postgres query where a time range as well as IN values

I have a table that gets around 20 million new records a day for positions of vessels. I want to query for a couple of different vessels and get the records for any period. As I'm using this for ...
0
votes
0answers
20 views

Why does MySQL tend to avoid indexes with few non-NULL values, even when it has a large penalty?

Over the last upgrades (5.6 -> 5.7 -> 8.0), I've observed that MySQL increased its aversion to indexes with few non-NULL values. This caused some seriously underperforming queries that I needed to ...
0
votes
0answers
29 views

Query Optimization - Potential ill effects of adding an full text index to a frequently updated table

We have a query of the following type which we are trying to optimize. SELECT table_one.* FROM table_one INNER JOIN table_two ON table_two.fk_id = table_one.id WHERE (table_one.h_id = 20626 AND ...
0
votes
0answers
19 views

optimize query with date type field in mysql

I currently have the following query prepared: select sum(amount) as total from incomes where (YEAR(date) = '2019' and MONTH(date) = '07') and incomes.deleted_at is null When ...
1
vote
2answers
40 views

Why is this query sometimes slow, and what can I do to make it faster?

I have a query to bin some log lines on a pre-processed "timeslice" field (this is unique every 15 minutes, for example "2018-04-11-15-3-3") There are approximately 6,000,000 rows in the database. ...
0
votes
0answers
31 views

How can I obtain a list of our applications' SQL statements that do not make use of indexes?

We have a system that processes thousands of statements a second, all running quickly. With so many statements we are looking to fine tune with small improvements to have a knock on large improvement ...
1
vote
2answers
78 views

primary key constraint Sorting

Is having a field in primary key constraint optimizes sorting of the table ? Here's the schema of the table: CREATE TABLE Persons ( Field1 long NOT NULL, Field2 long NOT NULL, Field3 long NOT NULL, ...
2
votes
0answers
52 views

View with where clause slower than query with same where clause

This query with periods (PeriodStart,PeriodEnd,ticker,FundamentalId,dimension) as (select b.reportdate PeriodStart, MIN(a.reportdate) PeriodEnd,b.ticker, b.FundamentalId,b.dimension from Base a ...
0
votes
0answers
22 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 ...
6
votes
1answer
479 views

Why SQL does not use the indexed view?

I have the following version of SQL Server: Microsoft SQL Server 2014 (SP3) (KB4022619) - 12.0.6024.0 (X64) Enterprise Edition (64-bit) on Windows NT 6.3 (Build 9600: ) (Hypervisor) I created ...
-2
votes
1answer
28 views

Should I use the LIKE predicate or fetch all rows and process them on the client?

I am optimizing my application, while this task, I find one query which is fetching data from database using like . and database rows are around 10000. So is this a good way to use like in query or I ...
0
votes
1answer
20 views

MySQL - Optimize subquery in Select, where and order by

Im running a query like below. SELECT DISTINCT c.`id` , c.`job-cat` , ( SELECT COUNT(*) FROM `jobs` j WHERE j.`mul_category` IN (c.`id`) AND j.`ending` >...
2
votes
2answers
74 views

Example of query execution plan where the cardinality estimation makes a difference

I am trying to create an example of a query execution plan which shows that if I have the cardinality estimation for some column of a table I can decide which query execution plan has the least cost. ...
0
votes
0answers
15 views

MySQL view extremely slow despite indexes

I'm having an issue with a view, when I make a query it brings CPU to 100% and it keeps loading. The view is: https://pastebin.com/ZgB7rr91 channel_data_60 table contains about 1,200,000 records: ...
0
votes
0answers
19 views

Why is turning on Event Scheduler Maxing my CPU?

I have search the web for the last week trying to figure this out. I have exported my tables and events, re-installed the lastest MySQL and imported. I have tried checking individual procedures. My ...
0
votes
1answer
41 views

SELECT optimization in Postgres 10

I want to select last unique rows based on time, so: SELECT DISTINCT ON (track) * FROM eco.tracks WHERE id > (SELECT id FROM eco.tracks WHERE time_track < ((SELECT time_track FROM eco....
7
votes
1answer
282 views

.NET executes a SQL query and Active Monitor shows multiple rows blocking each other

I use .NET to execute SQL operations on SQL Server 2014, here's the code used: using(SqlConnection conn = new SqlConnection(connectionString)){ //https://stackoverflow.com/questions/1880471/...
0
votes
1answer
35 views

How to link customers and suppliers in a database

Some customers can be suppliers at the same time, so: how to design the database so we don't have two tables having the same columns, and, at the same time, we can link a customer and a supplier ...
8
votes
2answers
138 views

Very similar queries, vastly different performance

I have two very similar queries First query: SELECT count(*) FROM Audits a JOIN AuditRelatedIds ari ON a.Id = ari.AuditId WHERE ari.RelatedId = '1DD87CF1-286B-409A-8C60-3FFEC394FDB1' ...
1
vote
1answer
36 views

Optimize MySQL query retrieval from audit log table

I am running MySQL InnoDB 5.6. This is my first post, so please bare with me of any mistakes. I have this schema: I have a list of invoices in the invoice table, and if I want to see all the ...
0
votes
1answer
30 views

Fetching rows from million rows table(Optimization)

I have millions of rows in a table of Greenplum database out of which I have to fetch around 45k rows and store them in a python List. It's taking more than 2hrs to fetch the data. How can I optimize ...