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
2answers
101 views

The difference between filtering a specific column in 'On Clause ' of a join statement and 'Where clause'

I have two tables with these structures: Table1(Date ,Code ,Name) Table2(Date ,Code) I need to filter a specific date for both tables. For instance I need data only for '10/28/2019' from each table....
1
vote
1answer
62 views

Query plan for a dynamically built `query_int` using `format()`

To make things simple here is a statically built version of the query that runs perfectly fast: SELECT * FROM media WHERE tag_ids @@ '123&321'::query_int ORDER BY rating DESC LIMIT 20; The catch ...
0
votes
1answer
33 views

Mysql - query optimization

I have a DB schema as such, SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='...
0
votes
0answers
92 views

Why query mysql 8.0 response too slow with respect to mysql 5.5

I have the following query which in mysql 5.5, original server runs in approximately 3 seconds but all the opposite with respect to mysql 8.0, which runs in about 3 minutes, a big difference!!!. This ...
0
votes
1answer
170 views

MySQL JOIN query takes too much time to get the result

I have a MySQL query as follows which JOINs 8 tables. When I use 3 tables to get the data the result is getting within 10 seconds. But whenever I add one more table the fetching time goes upto 1 ...
1
vote
2answers
302 views

Mysql: Is it possible to handle 1000 concurrent while having low cpu? [closed]

We're running Centos 7, we're in the process Mariadb 5.5, upgrading to 10.3 today. PHP 5. The queries are bad, and there are far too many of them to optimize, but I'm trying what I can, the database ...
-1
votes
1answer
360 views

Aggregate function on subquery

I have got a SALES table that has 4 document types, and they are created with reference to each other in the following order: Offer -> Order -> Delivery -> Invoice. For each new row their ...
1
vote
2answers
60 views

Optimizing a query by adding an index

Here are my tables: accounts_company: - id ... accounts_companyview: - account_id: FK to company - viewer_id: FK to company shipments_address: - companyview_id: FK to companyview - is_shipper:...
0
votes
1answer
77 views

How to optimize stored procedure so it won't end with “Out of memory exception”

I have created a stored procedure that check against 2 and then delete from the 3. When I execute the stored procedure on Management Studio I get the exception as mentioned in this question: ...
0
votes
1answer
79 views

Without rewriting this query, is there a way to force the query optimizer to better estimate the amount of resources required, e.g. with query hints?

I know there's a couple of alternate ways to write this query, but I'm more curious if there's another approach that I could use to improve the performance by helping guide the optimizer in the right ...
0
votes
1answer
65 views

Column as an Included column or as a key column

I have a query which has an optional where condition on column classID(not unique). This same classID is being displayed as a select column in various other queries. Currently i have classID as an ...
0
votes
3answers
80 views

Query takes too much time to execute

I add the Query Plan of my query. And this takes too much time to execute and give data. https://explain.depesz.com/s/4JtZ I want to reduce the execution time of this query. So require to optimize ...
0
votes
0answers
95 views

ORACLE MULTIPLE TABLE INDEXES ON GROUP BY

I have a VIEW with multiple tables, it has a group by statement containing multiple columns from each table. The problem is access to the view takes 4,5 seconds and ideally should take less than 1. ...
2
votes
0answers
39 views

Why can't outer join simplification happen for embedding outer join?

The MySQL documentation here suggests the following: Sometimes the optimizer succeeds in replacing an embedded outer join operation, but cannot convert the embedding outer join. The following query:...
0
votes
2answers
122 views

Horribly performing query, how to fix

select user_id, count(id) as unread_count from notifications where is_read = false and user_id in(select(unnest('{200 user IDs}' :: bigint[]))) group by user_id; Problem is, ...
0
votes
0answers
49 views

Mariadb query optimization - Procedure

I have a complicated procedure and I have 200 clients. When these 200 clients run the procedure simultaneously, the CPU value suddenly increases to 70%. Then it's 6% again. How can I optimize these ...
0
votes
1answer
38 views

PostgreSQL planner not using PK index

I have a query: select F.DVALUE as f1, VF.VAL as f2, f_strlen( VF.VAL ) as f3 from PR_STR_TO_LIST( '37593' ) L left join DUALS U on U.NUM >= 0 and U.NUM < 2 left join ...
1
vote
1answer
41 views

WHERE IN subquery too slow

I have a FULLTEXT search on the database, with the WHERE IN condition which checks that search results exists in another table. The query takes more than 1 hour to complete (companies table has 67k ...
8
votes
0answers
249 views

Constant Scan applying

This question is continuation of optimizer behavior exploration in regard to VALUES construct started here and here. I want to ask about VALUES and APPLY this time. Using CROSS APPLY to alias an ...
0
votes
0answers
28 views

How does MySQL optimizer compute join cost when indexed?

So, The standard formula in theory is C(R1.R2) = C(R1) + T(R1)*C(R2) where, C(R1.R2) - Cost of joining two relations (tables) R1 and R2 C(R1) - Cost of reading relation R1 C(R2) - Cost of reading ...
0
votes
1answer
237 views

Parse a unix/epoch value as a `TIMESTAMP` in PostgreSQL

PostgreSQL's COPY command is useful to quickly import lots of data, and the data must be in the textual representation for the datatype. I am importing lots of data, includes a timestamp column, but ...
1
vote
0answers
43 views

MySQL query optimization for a slow stored procedure

Edit: I am using MySQL 5.7.22 I have a stored procedure, when it is running it will have up to 1 millions rows examined. Which cause the 99% cpu usage. I want to optimize the query but I have no ...
0
votes
1answer
29 views

Mariadb query time extremely increased after upgrade from 10.2 to 10.4

After upgrading from Mariadb 10.2 to 10.4 extremely increased query time. On 10.2 query time is 0.5s. On 10.4 query time is 14s. Also query plan has changed too. 10.2: -------------------------...
3
votes
2answers
100 views

Why max(id) with join not working or how to optimize it with join?

Below is my MESSAGES_THREAD table: CREATE TABLE `messages_thread` ( `id` varchar(64) NOT NULL, `creator` mediumint(9) NOT NULL, `other` mediumint(9) NOT NULL, `time` datetime DEFAULT ...
0
votes
1answer
72 views

Determine least number of columns required for unique (clustered) index

I'm in the situation where I am encouraged to tune an existing database and in the process either introduce or remove indexes as seem best fit. I was looking at a certain table and noticed that the ...
1
vote
3answers
103 views

View: Ignore a left join if it is not used?

I have a web service that references a view called gis_sidewalks_vw. create table gis_sidewalks ( id number(10,0), last_edited_date date ); insert into gis_sidewalks (id, last_edited_date)...
2
votes
1answer
84 views

Slow Postgres Aggregate Query

I need to calculate values for a series of measurement data. The measurement data is divided into 1000 groups of consecutive rows as each call of the query must output a series of 1000 values. Typical ...
-3
votes
1answer
70 views

Why we can't have more than one inequality condition in MySQL indexing? [closed]

I am recently studying about database indexing in MySQL. I read some articles and found a great web page: http://mysql.rjweb.org/doc.php/index_cookbook_mysql I read in this page and some others that ...
0
votes
0answers
11 views

Storage technique used in meet in the middle attacks

When a block cypher is used twice in a row, it can be broken in 2^{k+1} key searches (k denotes the number of bits used in key). This computational advantage comes at a price of storing 2^k pairs of ...
2
votes
2answers
118 views

Performance optimization: Alternative to LEFT JOIN the same table twice

I would like to extend the following query with the commented out LEFT JOIN. However, those lines make the query noticeably slower. The query is used for an API to lookup user IDs by phone number and ...
0
votes
0answers
27 views

Trying to identify if the query is long running or long waiting

I have a sql query which has multiple joins and uses a view which has multiple joins.I captured the query in sql profiler and i see few information in there. I am trying to understand whether the ...
1
vote
1answer
68 views

Speed up complex join with disjunctive condition

I have a table that contains information about orders made by users and their phone numbers. The table looks like this one: table Orders ( OrderID int, UserName varchar(50), PhoneNumber1 ...
0
votes
0answers
78 views

optimization tips recommended for this query

I have these queries select decode(sum(txn_amount),'',0,sum(txn_amount)) as txn_amount, count(txn_id) as txn_num, currency_code, (select rpt_descr from rptcodesmtb where rpt_code = txnmtb.txn_type ...
1
vote
2answers
116 views

Join multiple tables for aggregate or create a single table with combined attribes

I have the following ER diagram. Now i have option of going with a single table ARTDATA as mentioned, or, I can go with 13 different tables linked with each other with relations. Problem is for ...
1
vote
2answers
78 views

MYSQL: How to optimize a date sorted table with a triple primary key index

I have been maintaining a MYSQL database for a few years, dating from back when i took database managing classes. Its biggest table has since grown (15 million entries for 1,8 GB) and this query began ...
-1
votes
1answer
100 views

How to optimize a slow select query with tuples in WHERE clause

I am currently trying to optimize a select query which takes 5-10 seconds on tables with 50k rows (and will grow 100x this soon). The purpose of this query is to find which tuples from a given set of (...
0
votes
1answer
46 views

Optimizing mariabb/mysql queries as a DBA

I'm maintaining a database that's used by a bespoke software application that has some performance issues. On of the worst offenders is query (3) below, and I'd like to, if possible, optimize the ...
0
votes
2answers
57 views

Why is Primary Key not present at the end of Unique Key in MySQL?

I have a table with following definition. CREATE TABLE `test` ( `a` int(11) NOT NULL, `b` int(11) DEFAULT NULL, `c` int(11) DEFAULT NULL, `d` int(11) DEFAULT NULL, `e` int(11) DEFAULT NULL, ...
13
votes
1answer
210 views

Сardinality estimation of partially covering range predicates

At the moment I'm trying to figure out how SQL Server evaluates the cardinality of range predicates that partially cover the histogram step. On the Internet, at cardinality-estimation-for-and-for-...
2
votes
2answers
566 views

MariaDB Optimization - insert and select

I have 2-3 php files. I am sending sms to users by sending parameter in these php files. I'm checking the 3rd and 4th table before sending sms so select. If there is an id record, I do not insert. If ...
2
votes
1answer
87 views

Range Extraction Algorithm for Muti-Part aka Composite Indexes in MySQL

I went through Range Extraction Documentation for MySQL Indexes. They have explained for single column indices. What is the procedure to reduce complex multi column (at least two) criteria? What I am ...
0
votes
2answers
36 views

DML queries with different performance on similar servers

I am measuring the performance between two servers that serve a database via MySQL 5.6. The DB1 server is a dedicated database server and the DB2 server shares resources with other services such as ...
0
votes
0answers
24 views

merge statement taking 30mins for 1777820 rows? how can i optimize it

merge into apl_scp_direct_dispatch t Using (select siteid, item, bucketid,band_qty from i2_scp_inventory_bands@DB_DMSAPIL_LINK where band_type = 'DEPBAND' and band_level = 2 ) s ON ( s....
4
votes
2answers
244 views

Ms sql MERGE INTO locks whole table for updates

I have a table for statistic values, it holds millions of records, which is defined like this: CREATE TABLE [dbo].[Statistic] ( [Id] [INT] IDENTITY(1, 1) NOT NULL , [EntityId] [INT] NULL , [...
0
votes
0answers
43 views

Optimizing range join (x between a and b) in MariaDB

My query has a join using a BETWEEN clause and it causes a full table scan to occur if I understand the EXPLAIN output correctly. Query EXPLAIN SELECT g.id, g.name, c.id AS child_id, c.date ...
-1
votes
1answer
80 views

Simple “SELECT” query taking too long

I'm trying to run a SELECT query on our PostgreSQL database using pgAdmin 3. It looks like this: SELECT tstamp AS "time" dvalue AS "Weight" FROM tablename WHERE tstamp BETWEEN 'time1' ...
0
votes
0answers
33 views

In a trigger, does assigning a column to the “old” value incur a cost? (Oracle) [duplicate]

For the sake of readability, in one of my triggers I have :new.COLUMNNAME := old.COLUMNNAME; alongside the sets for the rest of the columns. Does this statement get removed before runtime? (That ...
1
vote
1answer
133 views

PostgreSQL planner over estimates and self join query large table optimization

PostgreSQL 11.5 4 core, 32gigs 250gig SSD Not using default configuration, tuned to fit system memory and cores. Table+index size 14Gigs I'm working on a query that self joins to a table via a ...
0
votes
1answer
63 views

optimize query — remove nested selects

How can I rewrite the query to increase performance? Here is the query: SELECT flight.*, DAYOFWEEK(flight.flight_time) AS day_of_week, IF(SUM(...
0
votes
0answers
131 views

Advantages and disadvantages of JSONB columns

I love using json columns in PostgreSQL, but I'm wondering if there are scenarios where jsonb would make more sense. For example, we store a "seen by" state in our messages table and need to use ...

1
3 4
5
6 7
38