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
2answers
42 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, ...
-1
votes
0answers
14 views

PostgreSQL index scan is slow [duplicate]

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' ...
13
votes
1answer
143 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-...
0
votes
1answer
49 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
58 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
25 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
23 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
116 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
33 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
51 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
18 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
50 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
28 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 ...
0
votes
1answer
43 views

Query runs in serial and parallel mode in two identical databases on the same instance [duplicate]

I have two databases in the same SQL Server instance. One is a test database with a smaller number of records and total size of 500GB. Other one is production database with a size of 2.2 TB. Both ...
0
votes
1answer
26 views

Mysql Query Optimization [Match Against] [closed]

I have tried all the possible option to optimize the query, but no luck. Query 1: SELECT *, ( 3959 * acos ( cos( radians(51.5073509) ) * cos( radians(...
8
votes
2answers
320 views

Why doesn’t this recursive CTE with a parameter use an index when it does with a literal?

I am using a recursive CTE on a tree structure to list all the descendants of a particular node in the tree. If I write a literal node value in my WHERE clause, SQL Server seems to actually apply the ...
2
votes
0answers
18 views

Querying a High Cardinality Field

I am designing a data model for our orders for our upcoming Cassandra migration. An order has an orderId (arcane UUID field) and an orderNumber (user-friendly number). A getOrder query can be done by ...
0
votes
1answer
43 views

How to find all participants of a chat thread?

In my database I have the following tables: user, post, post_participant, and post_reply, largely as inspiration from this answer. One thing I'm having trouble with figuring out though is how to find ...
0
votes
1answer
20 views

Mysql performance -optimization(insert,select,proc) [closed]

Summary How can I receive, process, and insert 1000 rows every 5 seconds? Details I use the database extensively. We have vehicle tracking software. According to this software, a tool adds ...
0
votes
1answer
25 views

postgresql different configuration for different tablespace

I'm configuring new server with both SSD/HDD disk with ubuntu 18.04. I've installed system in SSD and created two different tablespaces one in ssd and one in hdd. I would like for configuration ...
2
votes
1answer
41 views

How can performance of queries containing keywords like IN, EXISTS, etc. be optimized?

I was interested in some of the general ways one can use to optimize the performance of queries that use keywords like IN and EXISTS (relying on the state of the entire database). For example, ...
4
votes
1answer
460 views

Disk usage of integer column vs boolean column in Postgres

I saw this question Bit vs. Boolean columns. I'm asking myself the same for Postgres: does a single digit integer column occupy the same disk space of a boolean one? In big tables (~50 columns x ~50 ...
1
vote
2answers
68 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
1answer
40 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
21 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
19 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 ...
4
votes
1answer
111 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
44 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
42 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
45 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
22 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
43 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
51 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
17 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
30 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
171 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
40 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
52 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
107 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
39 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
84 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
121 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
92 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
92 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 ...