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
-2
votes
1answer
33 views

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

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 ...
-1
votes
1answer
27 views

Performance using a jsonb column in PostgreSQL?

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
76 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
71 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 ...
3
votes
1answer
83 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
36 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
86 views
+100

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
26 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
19 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
39 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
28 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
37 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
72 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
44 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
425 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
27 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
15 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
72 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
40 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
280 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
135 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
35 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
28 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 ...
0
votes
2answers
25 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 ...
1
vote
1answer
36 views

how to override a table's cardinality or statistics histogram

I remember reading once that it is possible to force SQL Server to assume a table is a certain size, or perhaps even override a statistics histogram, when compiling a query. Can someone explain how ...
0
votes
1answer
30 views

Does “consolidation” of query statements improve query performance?

Assume we have a set of postal addresses A1, A2, A3, ... of the form: A1 = 124, "Smith", "Rd" A2 = 142, "Jones", "St" A3 = 8190, "Smith", "Ln" A4 = 142, "Via Gracia", NULL A5 = 190, "Jones", "St" A6 =...
2
votes
0answers
60 views

What's the more efficient way of selecting with multi left joins? [closed]

I have 4 tables, the first one is "student", the other 3 tables are different records of the student's activities. All tables have a id column as the primary key. student.id is varchar(64) (similar ...
1
vote
1answer
56 views

Optimize MySQL query and index for multiple column range query

I have a table like this: CREATE TABLE IF NOT EXISTS `jobs` ( `job_id` varchar(36) NOT NULL, `job_status` varchar(30) NOT NULL, `created_at` datetime NOT NULL, `lease_date` datetime, ...
0
votes
0answers
21 views

Optimizing the read of child records of many parents

The main table has a complex query that selects the jobs that are to be displayed on the current workstation. In addition to the main job record there are a couple of child tables with a few dozen ...
0
votes
1answer
77 views

Nested case statements vs multiple criteria case statements [closed]

Had an interesting discussion with a colleague today over optimizing case statements and whether it's better to leave a case statement which has overlapping criteria as individual when clauses, or ...
1
vote
1answer
42 views

How to automate basic ddl scripts execution and management [closed]

Looking for suggestions (A Script, third party tool, software etc.) which can help automate some of the administration tasks around execution of DDL scripts across environments. Database is usually ...
0
votes
0answers
19 views

Composite index to cover sum of columns

Is it possible to create an index in MySQL that will help with a query like this: SELECT a, b, c FROM `table` WHERE a + b + c > 0 I've tried adding a composite index on all a, b, and c to no ...
1
vote
0answers
57 views

How does mysql optimizer works?

I have two tables t_core_blueprints,t_ci. CREATE TABLE `t_core_blueprints` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `last_modified_on` ...
0
votes
1answer
41 views

Oracle - large table with large IN

Table students has about 10 million records; ID is indexed. Student grades has 20 million records - student_id is indexed. I am querying about 20,000 students by their ids ids with a join between ...
0
votes
1answer
40 views

Speed up slow postgres query with indices (~5 minutes)

I have roughly 100 million rows in a table called 'bets' and a couple million in 'games'. The problem is that even with indices particular queries run very slow. The tables are structured as so: ...
2
votes
2answers
173 views

Should I split timestamp parts into separate columns?

I am building a PostgreSQL database and I have created a timestamp table, where the primary key is the timestamp itself (e.g. id: Fri Apr 13 2018 15:00:19). The database is supposed to be later ...
0
votes
1answer
23 views

Analyzing a query plan for a query with TWO predicates, but index only has ONE

Note: This is from an introductory database systems course at my university so I'm not entirely sure if it matters, but for what it's worth we are being taught MySQL. One homework question I'm doing ...
1
vote
1answer
26 views

Mysql Query Optimisation For Joining a BETWEEN Date and Date query

I have some instagram API data sitting across two tables. One with post_data, and one with profile_data. The profile_data table has a timestamp of when the account was checked through the API to get ...
0
votes
1answer
20 views

mysql 5.6 is Not referring Indexes for deterministic function DATEDIFF

I do not understand why MySQL is not using the index for the below query. I have a table named users and its create table is this table contains data. Now I am running SQL select statements and it'...
0
votes
1answer
65 views

How to optimize a SQL Server query to fetch the results faster

Currently I am having a situation where I have to import around 0.5 Million rows into a SQL Server table named BARCODE_MANAGEMENT, which has the following columns: ID int (identity column), SETUP_ID ...
3
votes
3answers
215 views

Query to categorize rows based on a “time” column without using a CASE expression

There is a ProductTT table as you can see below: [dbo].[ProductTT] (ID int , Product Varchar(50) , Time Int) ...which contains the following rows: 1 XX 0030 2 UY 0354 3 YY 0517 4 ZZ ...
1
vote
3answers
43 views

Detect one-sided matches

I have a SQL Server 2014 database table containing a code and two identifiers: DECLARE @Foo TABLE (code CHAR, id_a INT, id_b INT) INSERT INTO @Foo (code, id_a, id_b) VALUES ('A', 1, 2), ('B', 2, 1), (...