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
889 views

How does MySQL store data, can I detach a mysql Database?

I have used SQL Server in the past which each database is kept in separate files (a data file and a log file) but things are different in MySQL as it seems. The file sizes of these files translates to ...
2
votes
1answer
27 views

Postgres condition pushdown when using range conditions (>=, <= or BETWEEN)

I want to understand better the circumstances under which Postgres query executioner pushes conditions down into the join components. I would be especially grateful for references to source code parts ...
6
votes
1answer
276 views

Demystification of SQL Server optimization process [duplicate]

We would like to see all variants of query plan considered during a query optimization by a SQL Server optimizer. SQL Server offers quite detailed insight using querytraceon options. For example ...
2
votes
2answers
1k views

Optimize large table design

First, I am a complete novice when it comes to databases, but have been given the task to speed up queries on a large set of data. (Hundreds of millions of records) The current implementation is a ...
3
votes
4answers
199 views

Optimizing slow query

For a few days now, i'm trying to optimize this query. But still I haven't found the right solution in how to fix this. The contents table contains about 20 million records. As the connect table ...
0
votes
1answer
40 views

Does postgres optimise repeated expressions?

select column_1, (column_1 <-> cube(:input)) / (:input <-> cube(0)) difference from table_1 for this query, is calculation of :input <-> cube(0) repeated for every row? Also, for ...
0
votes
1answer
220 views

Ways to optimize the PostgreSQL/TimeScaleDB query

What approaches I could take to optimize the performance of the following joinless query on the following PostgreSQL/TimeScaleDB table? So far, I managed to create the right index which is obviously ...
2
votes
1answer
117 views

Index or query tuning for complex predicates

I'm struggling to identify effective indexes (or rewrite the query) to improve a query with the following confounding predicates: JOIN on a date from one table being in range - between two date ...
0
votes
1answer
193 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 ...
2
votes
1answer
385 views

MySQL using variable in SELECT command with view affecting performance

sorry if this has been asked already. I've just moved from MySQL 5.6 to 5.7.17 and I'm struggling to understand this issue. I have a view "delete_me_test_" which has around 26k rows. SELECT ...
1
vote
1answer
636 views

MySQL: Performance issues in heavy multi-threaded environment

I have 23 of these tables in one database: CREATE TABLE foo1 ( DateTime datetime NOT NULL, BidOpen decimal(11, 5) NOT NULL, BidHigh decimal(11, 5) NOT NULL, BidLow decimal(11, 5) NOT NULL, ...
4
votes
3answers
2k views

Optimise Large Record Deletion Procedure

I'm creating a database that mirrors our production database, but is lighter and anonymised - for local development purposes. Ensuring we've enough data to operate on as an engineering team, I'm ...
1
vote
2answers
12k views

MySQL to use all 20 cores I have

I am currently setting up a website that uses a MySQL database having more than 40 million rows of data. I am using one server for files and separate mysql server. Two servers have 20 cores, 64G ram. ...
1
vote
0answers
186 views

Slow MySql with left join and Group by

Below is the Mysql 5.7 Table query which has a left join with the group by. When I execute it takes too much time. line_items has around 3.5M data and users have 1000 rows. Below is an index of my ...
0
votes
2answers
110 views

mysql query performance… got to be a better way

I have a query right now that for a lack of a better word, sucks. I'm looking for some help with respect to other ways to approach writing this query. Here are the details. I've got a temp table ...
0
votes
2answers
347 views

MySQL - How to optimize multiple OR conditions in a query on a large table

I have a table with the following structure: Configuration ( id: binary(16) Primary key, htId: binary(16), raId: binary(16), date, amount ); I have already created these indexes: ...
0
votes
1answer
2k views

Costly HASH MATCH Aggregate

I have a view,which is slow when i query a simple select statement with a where clause.The where clause column is indexed(Non-clustered index) Here is the view and the plan. execution plan CREATE ...
0
votes
2answers
493 views

How to optimize information schema table in mysql?

When I checked slow running queries log, I found out that select count(*) into @discard from information_schema.PARTITIONS; This query is taking 6 seconds to execute (a bit higher for this execution)...
1
vote
1answer
95 views

MySQL - Log data with Inner Join 12 Tables or Create View?

I'm using MySQL (phpmyadmin) with engine InnoDB for my e-commerce website. Now, I need to get the data as Log but It's need to joining more than 12 tables for logging all data we need to see, only ...
3
votes
1answer
113 views

PLSQL using cursors or functions to query repeated data

Is it better to declare a commonly used lookup query as a package function or as a function (either returning rows or values or returning a sysref cursor)? Whats the difference? What are the best ...
0
votes
2answers
121 views

Best way to delete large set of rows knowing field to NOT delete

Coming from a Rails background, I have a large production database that I have a copy of where I only need records from 4 of the 2000+ companies. I'm trying to delete all the rows except the ones ...
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 ...
20
votes
3answers
34k views

How to speed up select distinct?

I have a simple select distinct on some time series data: SELECT DISTINCT user_id FROM events WHERE project_id = 6 AND time > '2015-01-11 8:00:00' AND time < '2015-02-10 8:00:00'; And it takes ...
5
votes
2answers
106 views

Why does SQL Server not perform constant (UNION ALL) branch elimination with OPTION(RECOMPILE) when selecting the result into a scalar variable?

We use some 'aggregate' views to select from multiple tables using a discriminator (note: these views are not partitioned views, because the discriminator is not in the base tables). This normally ...
0
votes
1answer
109 views

Having four different tables or having one table with one more field?

I have a website with 4 translation (That is Arabic with 4 different Persian translation), and I had exported each of these translations to different tables. Which one of these ways are faster and ...
0
votes
1answer
163 views

Is it advisable to asynchronously update the summary table every time a transaction is deleted?

Given three tables: (a) Product (b) Transaction Head (c) Transaction Detail We need to create a query that fetches a list of products with associated latest ordered quantity and latest transaction ...
0
votes
1answer
195 views

Why a query takes too long in statistics thread state in AWS Aurora MySQL?

The following query execution too long in statistics state and I couldn't figure out why. DB engine - 5.7.mysql_aurora.2.07.2 DB Size - db.r5.4xlarge Sample Query Profile output +----------------------...
2
votes
1answer
52 views

MySQL performance issue when a hash join is used

I'm having an issue where the optimizer seems to be doing a full table scan and exhibiting a big performance hit when a hash join is used. Schema and sample data: CREATE TABLE `px_po_status` ( `...
4
votes
1answer
8k views

Correct use of VOLATILE COST (and ROWS) indications in Postgresql stored procedure

While looking at several examples of pl/python and pl/pgsql, I have seen many - but not all - using volatile cost. ie: CREATE OR REPLACE FUNCTION my_function() RETURNS setof record AS $BODY$ -- ...
2
votes
1answer
33 views

MySQL performance issues with occasional complete freezes

we have a fairly large production DB currently at ~50GB but with constant growth. In the last couple of weeks, we are struggling with performances as some queries are getting very slow even if indexes ...
0
votes
2answers
71 views

Cardinality , Parallel Hint alternative for MS-SQL

UPDATE MARK M SET ARCHIVE_FLAG = 'N' WHERE EXISTS (SELECT /*+ cardinality(S1, 10) parallel(S1,8)*/ 1 FROM SHFASG S, SHIFT S1 WHERE S.ID = S1.ID AND M.ID = S.MARKID AND ARCHIVE_FLAG <> 'Y'); ...
0
votes
2answers
43 views

Optimizing nested SQLite query

I have a SQLite table named hashbands that looks like this: hashband | file_id | window_id ------------------------------ potato | 0 | 0 potato | 1 | 0 The table has 100M+ rows (lol). ...
15
votes
2answers
2k views

Optimization: Moving variable declarations to the top of your procedure

While working on optimizing some stored procedures, I sat down with the DBA and went through some stored procedures with high blocking and/or high read/write activity. One thing the DBA mentioned was ...
0
votes
1answer
364 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
113 views

Fiscal Year to Date filter

This is my data: I need a Fiscal Year to Date logic that will gather all previous months starting October. However, for October 2018 I want to start form 15th, for remaining years it must start from ...
1
vote
2answers
467 views

What is the recommended my.cnf settings for 32 GB Server

I am just moving my website to a new server and ı want to know what is the recommended settings for 32 GB server? Mysql tunning for my old server (16 GB) https://pastecode.xyz/view/577e824e
3
votes
1answer
158 views

Commit() takes 50 seconds after inserting about 100000 rows

I have a sqlite database that is, (I think), fairly small. I has about 1 million rows in a couple of table and the total space on disk is about 150 Mb. I then open a transaction and insert about ...
0
votes
1answer
232 views

Query Tuning and Index Tuning - SQL Server

I've been asked to improve the performance of the following query. I can't amend any existing Indexes but I can create new ones. The table structures can be found in this SQL Fiddle The row sizes of ...
1
vote
2answers
48 views

How to optimize a query with timestamp that using OR clause?

I have the follow table: create table myTable ( id serial not null rowtype integer ...
2
votes
1answer
274 views

MySQL InnoDB or MySQL NDB Cluster which one to use

We have an application with 8000 users accessing in parallel(max). We are trying to bring in horizontal scaling. I came across MySQL NDB cluster. Should I go with it? Just for auto sharding and other ...
4
votes
4answers
348 views

SQL Server Optimization/Configuration for Inflexible Application

I'm responsible for overseeing an application which uses SQL Server as its backend storage solution. Unfortunately, the application has no concept of how a RDBMS should be utilized and seems to use it ...
0
votes
1answer
134 views

ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes

An online optimization tool offered to add indexes. One of the index is successfully created but i cant create the second one. Do you think that indexes going to reduce query run time? Any ideas how ...
0
votes
2answers
2k views

MySQL crashes and currently the error.log file is huge

I've created a droplet on DigitalOcean created using Laravel Forge 6 months ago. Two weeks ago, we decided it was time to upgrade the droplet and we moved from a 4GB RAM/2CPUs to a 16GB RAM/6CPUs ...
0
votes
0answers
13 views

Optimising tracking page views Aggregation

select page.*, views.view_count from page join (select page_id, count(page_id) view_count from (select page_id, user_id, floor(EXTRACT(epoch from time) / EXTRACT(epoch from ...
0
votes
1answer
262 views

MySQL indexing issue when trying to search for a part of a string/word

What we're running into: Engine: MySQL. We've been working on a filter where an user can search for a reference number. However, we've ran into an issue with performance on bigger data sets. We ...
1
vote
1answer
123 views

MySQL optimization for mapping service — getting MySQL to use more memory?

Time to upgrade hardware? ... I'm not sure if there is much more I can do, spent a few days tweaking/googling -- before taking the upgrade plunge I thought I'd check here... to see if the well ...
0
votes
1answer
195 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

ola.hallengren - Alternative MySQL scripts!

Having worked with MS SQL for many years and found ola.hallengren scripts incredibly useful - I find my self in a MySQL environment. Is there a MySQL alternative to ola.hallengren? - Perhaps someone ...
2
votes
2answers
9k views

Group by WEEK but show a more readable the result?

I had this query: SELECT YEAR(datetime) AS Year, WEEK(datetime) AS Week, COUNT(*) AS total FROM table GROUP BY Year, Week; The result is: +------+------+-------+ | Year | Week | total | +------+----...
2
votes
1answer
650 views

How to count selectivity rows in PostgreSQL 8.2

I have problem with two queries on PostgreSQL v8.2. It seem like the optimizer chooses a wrong index: db=# explain select count(*), messagetype, alias from event where templateinfoid = 10 and ...

1
2 3 4 5
37