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
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
42 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
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
20 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
80 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
53 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
484 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
29 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
76 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
17 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
21 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
290 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
142 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
38 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 ...
0
votes
2answers
26 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
37 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
67 views

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

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
105 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
274 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
51 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
60 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
44 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
44 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
187 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
25 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
27 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
29 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
70 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
221 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
44 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), (...
0
votes
0answers
26 views

MySQL optimize table

I have a table which record the bandwidth usage in MySQL. Every minute I have a new record. Here is my table : +------------------+--------------+------+-----+---------+-------+ | Field | ...
3
votes
2answers
129 views

updating statistics using STATS_STREAM or WITH FULLSCAN?

the syntax for creating statistics is: -- Syntax for SQL Server and Azure SQL Database UPDATE STATISTICS table_or_indexed_view_name [ { { index_or_statistics__name ...
1
vote
0answers
61 views

Slow ORDER BY with large and small tables

I have one small table (200 rows) and two large tables (200k rows each). Small table has primary key id and some other fields. First big table has id field (not a primary key), which are duplicated, ...
1
vote
1answer
37 views

Optimize MySQL InnoDB query for SUM and Order By

I have an MySQL InnoDB table with 1.3M rows and 670MB size: +----------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------...
1
vote
1answer
79 views

Why are some count queries so slow?

I have a table users with 2.6 millions of rows and SELECT COUNT(*) FROM users takes 2 seconds to succeed, but request for table stories with 5.2 millions of rows takes more than an hour. Explains are ...
0
votes
0answers
39 views

Optimize query migrated from SQL Server

I have been working on SQL Server for more than 10 years, now I have a database which I want to confirm if it is viable to change it to PostgreSQL, this (simplified) query runs in about 100ms in SQL ...
3
votes
1answer
41 views

MariaDB configuration for many updates, writes

Based on few tutorials that I show I created the following MariaDB configuration for my server. Server has 32 GB ram, SSD, 8 cores and is dedicated to run MySQL. The biggest table has about 1,5 ...
1
vote
2answers
69 views

MySQL: need help making a query faster

I wrote a query that finds me records in discovery table that contain a needle from whitelisted (LIKE %needle%) and don't have a record in logs table (and some other easy to understand filtering): ...
0
votes
1answer
43 views

Optimizing MYSQL statements

Recently, I started working on a project, where I came across many MySQL statements like this: SELECT s.Important, c.Interesting FROM table1 s, table2 c WHERE s.IdForTableC = c.id or SELECT q....
0
votes
1answer
21 views

Query optimization: how to efficiently occlude overridden records?

Imagine a table with roughly a couple of billion rows. A simplified schema for the table would be started_on: datetime recorded_on: datetime image_id: integer It's indexed by started_on. One ...