Questions tagged [optimization]

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

368 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
0
votes
0answers
184 views

Postgres- comparing two arrays in the where clause optimization

I am comparing an array inside my table with an array that has the maching IDs. this query performs slowly as soon as the number of items in the passed in array gets larger. I was wondering if there ...
0
votes
1answer
37 views

MySQL: Use index in background query

I'm using MySQL 5.7.10 I'm checking a new query for an audit report. I'll execute it in a simple background Unix process, whick invoke mysql from the console. To check the query, I use a worksheet in ...
0
votes
2answers
64 views

Optimize query to not read all rows in table

I have this in my slow query log. Slow Query Log # Time: 2020-03-09T08:42:46.946084Z # User@Host: admin_admin[admin_admin] @ localhost [127.0.0.1] Id: 16 # Query_time: 0.001558 Lock_time: 0....
0
votes
1answer
71 views

optimizer uses all the wrong indexes instead of one right

This is another stupid question about optimizer. Synopsis is exactly as the subject is: the optimizer does it wrong. Seems like is has an absolute desire to use any index that contains REALM_ID field: ...
0
votes
0answers
47 views

Slow MySQL query speed

I have a problem with MySQL query performance. Please look at the details below. My question is if perhaps my schema could be simplified? This has to be done keeping generic device type support - ...
0
votes
1answer
18 views

What's the optimal way to query data using another table column as a between filter

sorry for the ambiguous question title. I don't know what's it called. The thing is let's say I've a order table storing uid , order_id and ip looks like this | uid | order_id | ip | 10000 | ...
0
votes
1answer
362 views

Implementing MySQL tuner suggesstions

Mine is GoDaddy VPS with 4CPU, 8GB -200GB Centos server with WHM. I am using latest Moodle with MySQL version 5.7.29 on 64-bit architecture. I was advised to tune DB for better concurrent usage. I ...
0
votes
1answer
118 views

Optimize table on large table online DDL Feature

I am running optimize table on MySQL table size 340GB in size and storage engine is InnoDB. mysql> show variables like '%tmp%'; +----------------------------+------------------+ | Variable_name ...
0
votes
1answer
70 views

Slow query when matching columns from second table in JOIN with GROUP BY

Update: so, as mentioned in the comments thread, it seems the ORDER BY might be the most direct cause of slow execution. So, the question should be: how can this query use ORDER BY score and execute ...
0
votes
2answers
35 views

MySQL: SELECT more data than necessary or use multiple SELECT queries?

I'm wondering if it would be better to use a single query and SELECT more data than I might need, or use multiple queries and SELECT only what I need when I need it. For example when the client ...
0
votes
1answer
31 views

Query with left join not using index

The below query is taking about 1 second to run SELECT `taxi_driver_mapping`.*, concat("dpk00", taxi_driver_mapping.mapping_payment_plan_id) AS package_text, `people`.*, ...
0
votes
2answers
111 views

Need help in optimizing a slow Postgres Query

So, I need to calculate a rolling Monthly active users on everyday for last 100 days. In output, one column should represent the dates of last 100 days from today and against every single day, number ...
0
votes
0answers
38 views

optimize query with fulltext index in mysql

I have a column inside my mysql table that has a fulltext index and I need to make a query in which I get all the rows that do not have a specific word or those that start with those letters, so I ...
0
votes
0answers
114 views

Small read cache on SSD (MySQL)

I have a question concerning performance settings/setup in MySQL DB/InnoDB. The server has SSD disks so general access to data is fast. Although there is enough of RAM, but enough is never enough and ...
0
votes
1answer
52 views

How to avoid index_merge in a query?

I have multiple queries using index_merge but for put an example, I show you the following query EXPLAIN select count(*) From Clientes Where id_campana='1849' and id_empresa='55' and (...
0
votes
0answers
33 views

Mysql: extremely weird index behaviour on “SELECT WHERE IN”?

I've just been stuck for a bit on a couple of very weird index choices from mysql. I have a table with half a billion entries and primary key is an auto inc ID Original query: "select id from table ...
0
votes
1answer
26 views

Optimal Table Structure

We have a parent table, Journal and a child table, Stops. A Journal can have X amount of Stops. For a good 80% of our SQL queries, our customers are just interested in the first and last Stop for ...
0
votes
0answers
12 views

Does the Query Optimizer have the right to create Temporary (Materialized) Views if it enhances performance?

Does the Query Optimizer have the right to create Temporary (Materialized) Views which can enhance performance? Sorry if this question seem very trivial. Or in other words, does the query optimizer ...
0
votes
0answers
32 views

SARGABLE predicate must be in DNF. Why?

This famous paper about finding Access Path in Relational Database Management System by Selinger et al. says the following about criteria tree and predicates (at the end of 3rd section). A sargable ...
0
votes
1answer
186 views

Procedure MySQL cursor taking a long time to execute

I have a MySQL procedure, with a cursor in it. The sample cursor query is given below DECLARE cs CURSOR FOR (SELECT a.mag_id FROM A a WHERE a.creation_date BETWEEN (...
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
256 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
3answers
90 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
126 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. ...
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
0answers
32 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
0answers
29 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 ...
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 ...
0
votes
1answer
47 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
39 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
45 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 ...
0
votes
1answer
49 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
192 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 ...
0
votes
2answers
373 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
91 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 ...
0
votes
2answers
65 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
0answers
31 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
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 ...
0
votes
0answers
26 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 ...
0
votes
1answer
26 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` >...
0
votes
0answers
39 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
1answer
43 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 ...
0
votes
1answer
42 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
170 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 ...
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
0answers
23 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 ...
0
votes
0answers
31 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 | ...
0
votes
0answers
40 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 ...
0
votes
1answer
45 views

Optimizing one-way syncing of large (wide) tree

I deal with several trees that can vary in size (they all have depths of ~8-10) but it's not unusual for them to consist of ~50-100k nodes. At the moment, the trees are stored using simple parent_id ...
0
votes
0answers
54 views

Optimize query for orders with unseen comments

I have following MySQL tables in an application I have to support: CREATE TABLE orders ( id int auto_increment primary key, author int(8) not null, manager_id int(10) default 0 ...

1
4
5
6 7 8