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
5 votes
4 answers
17k 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 | +------+----...
3 votes
1 answer
3k views

How to optimize a Full Text Search

So we have this VARCHAR(255) column called code, which is a string of varying size we use in a WHERE clause. Doing this search slows the query down by about 300ms and since the query is run a few ...
17 votes
1 answer
5k views

Why is Clustered Index Scan Number of Executions so high?

I have two similar queries that generate the same query plan, except that one query plan executes a Clustered Index Scan 1316 times, while the other executes it 1 time. The only difference between ...
0 votes
1 answer
76 views

Index Optimization for tables that are truncated and charged everyday

I'm a recently recruited Data Engineer, I have made a bunch of ETL pipelines planned to run daily, the destination tables are truncated and charged again. The DBMS is on-prem SQL Server. When I ...
0 votes
1 answer
1k views

How can Mysql server auto re-index tables

I have a table which has very minimal data. However the index_length is very very huge, because of frequent DML operations on this table. Ex : +-----------------------+--------+---------+------------...
0 votes
3 answers
916 views

MySQL Connections slows down my website

I have a website that runs perfectly normal, until today it becomes very slow. After a few hours debugging, it all points down to database. I ran these queries show status like 'Connections'; +-----...
4 votes
1 answer
186 views

Is there a more optimal way to create this view or stored procedure? Currently using a cross apply, but it's slow

I've updated this post with some test data. I'm creating a report for my Movies database where I'd like for the end-user to be able to select movies of a certain genre. However, some movies have ...
-1 votes
0 answers
27 views

Is a foreign key (FK) index "slower" when FK column is nullable (allows NULL)? (Postgres)

For a long time, I have held a belief that "nullable foreign key" columns are "slower" because the FK index will be slower. I read it somewhere once and it stayed with me. I cannot ...
0 votes
1 answer
396 views

How to optimize UPDATE with a nested SELECT subquery?

I wrote a complicated UPDATE query, and it works, but it looks menacing. Here's what I'm trying to do: In each topic user 'Bob123' posted anonymously. When you post anonymously in a topic, you get ...
0 votes
2 answers
161 views

SQL server alwayson 3 Node Primary Down

I am using three node alwayson Availability Group SQL 2012 Enterprise Edition. 2 Node in DC with File Share Witness quorum also in DC and third node to be setup in DR I need to perform a DR switchover ...
0 votes
1 answer
297 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
2 answers
672 views

Optimization causes tmpdir to fill up with 'deleted' file handle

Using Percona 5.6.34-79.1. Storage Engine is InnoDB. One of the table to be optimized is 300Gb in size. tmpdir is a separate partition, and datadir is separate. datadir has 500Gb of space, so ...
13 votes
2 answers
2k views

The use of NOT logic in relation to indexes

According to Microsoft's book on database development Exam 70-433: Microsoft SQL Server 2008 Database Development: Neither leading wildcard characters not NOT logic allow the query optimizer to use ...
0 votes
0 answers
12 views

Postgres 12 Move and Delete Data using (CTEs) WITH statements causing parsing issues - best approach?

Am not sure if this is the best method of moving/deleting data. I have 2 tables play and game, information from the play table is referenced from the game table. I am trying to find all games that are ...
0 votes
1 answer
64 views

Optimize filter on SQL query on PostgreSQL

I have a query with a high cost on filtering the results and I am thinking I should be adding an index to optimize the plan, but the indexes I tried till now had no impact. Can I optimize the query by ...
0 votes
2 answers
237 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
1 answer
191 views

Optimizing regex based query in sqlite

I'm using an sqlite database to store manually created labels for some data automatically queried from a live system. The data from the live system consists primarily of an address, comprised of 3 ...
6 votes
4 answers
6k views

Optimizing a simple query on a large table

DB: MySQL 5.5.20 (WampServer, default configuration) OS: Win 7 HDD: Western Digital 3TB Caviar Green, 3.5", IntelliPower, 64MB, Sata3 (WD30EZRX) Memory: 8GB MySQL my.ini: http://pastie.org/private/...
-2 votes
0 answers
16 views

MySQL select script is slow [closed]

Good day! I have a problem in MySQL and PHP. I have a script with only 16 data in the table. and I have a simple script. Sometimes querying is slow and it takes to 2minutes to display. I have 128 RAM ...
0 votes
1 answer
22 views

How to daily download and merge a csv file and update on Google sheets?

Disclaimer: I’m not sure if this question goes on here. I’ve trying to solve this issue for quite a while and I don’t think there is another way other than using a python script. I need to solve this ...
0 votes
1 answer
658 views

How to reduce redundant time-series data in MySQL into a function?

i have a question that actually does not fit here very much, but maybe it fits. So, i have data like this: How can i reduce this 3 entries with same values? Sure, i can just delete the other 2, but ...
1 vote
2 answers
263 views

CPU High usage crashing our server

The server where my database is running is suffering from CPU spikes. We're having trouble identifying what is causing these CPU spikes, and consequently how to mitigate them. I've tried adding some ...
0 votes
1 answer
406 views

UNION ALL vs CASE - SQL query optimization

I need to retrieve the count of both approved and unapproved data from my_table. And I have tried both queries and succeeded. Which query is optimized and why. Please give suggestion DDL Query CREATE ...
0 votes
1 answer
133 views

In Postgresql, can I create a column of a one-dimension array type with foreign key constraint? If not, how to simulate it?

In essence I have to design todo table, and an item table. I need to preserve item order, as well as allowing client to rearrange the order. If postgres allow an array of foreign key that would be ...
0 votes
1 answer
197 views

Nested loop on primary key

CREATE TABLE `request` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `created_by` int(11) unsigned DEFAULT NULL, `content` text, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; ...
-1 votes
1 answer
36 views

Optimization : JOIN or CTE [closed]

Suppose that I have these tables,and example data (the real table I am working on has more columns) table `users` id name zip 1 John 201 table `region` zip city state country 22 a b c table `...
1 vote
1 answer
58 views

Optimizing simple query in PostgreSQL (two table join and sorting)

I have a simple query that should return data about booking, but it misbehaves and sometimes does not return data within 1 minute. So I have main booking table: table booking_data - bid bigint primary ...
3 votes
1 answer
13k views

MySQL hangs in killed state

I have a strange situation where a mysql OPTIMIZE query will never end. After killinh it, it remains in the "killed" state. Scenario to reproduce: OS: CentOS 6.5 MySQL: 5.6 Engine: MyISAM (with at ...
0 votes
1 answer
1k views

MYSQL join on subquery optimization

This is a distilled version of the query that I am trying to run: SELECT * FROM table1 t1 INNER JOIN (SELECT * FROM table2 WHERE foo = 'bar') t2 ON t1.id = t2.id; The above query is incredibly slow ...
0 votes
2 answers
159 views

Index conditional aggregate query on PostgreSQL

How does one optimize a conditional aggregate query on PostgreSQL? A example of such query would be: SELECT SUM(score) FROM "games" WHERE "PlayerId" = 23 AND "status" = 'FINAL'; Indexing (PlayerId, ...
2 votes
2 answers
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 ...
0 votes
1 answer
158 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
2 answers
2k 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
2 answers
327 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 ...
124 votes
7 answers
136k views

How to determine if an Index is required or necessary

I've been running an auto-index tool on our MS SQL database (I modified a script originating from Microsoft that looks at the index statistics tables - Automated Auto Indexing). From the stats, I now ...
1 vote
2 answers
38 views

Could MySQL perform better on a 10GB network storage with raid vs local HDD?

Compared to using a HDD on local computer and do many MySQL read / writes on it, could it have more queries per second if I install 10GBe network cards and I store the MySQL data on a raid network ...
14 votes
4 answers
43k views

Where column not like multiple values

I am trying to select records in a postgresql db where username is not like a list of strings. SELECT * FROM rails_db WHERE username NOT LIKE 'j%' AND username NOT LIKE '%eepy%'; The issue is there ...
2 votes
1 answer
515 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 ...
14 votes
1 answer
11k views

Postgres: count(*) vs count(id)

I saw in the documentation the difference between count(*) and count(pk). I had been using count(pk) (where pk is a SERIAL PRIMARY KEY) not knowing about the existence of count(*). My question is ...
3 votes
4 answers
244 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 ...
17 votes
1 answer
469 views

Constant Scan applying

This question is a continuation of optimizer behavior exploration in regards to VALUES construct started here and here. I want to ask about VALUES and APPLY this time. Using CROSS APPLY to alias an ...
6 votes
1 answer
846 views

Do datatypes of columns affect query performance?

Let's suppose I have this table: Table "public.orders" Column | Type | Collation | Nullable | Default -----------------+---------------+-----------+----------+----...
1 vote
1 answer
40 views

Why is the optimizer not using the clustering index on my table?

I have this table Table "public.lineitem" Column | Type | Collation | Nullable | Default -----------------+---------------+-----------+----------+----...
0 votes
1 answer
51 views

How to speed up subquery

Let's suppose we have: select o_orderpriority, count(*) as order_count from orders where o_orderdate>='01/07/1993'and o_orderdate<'01/10/1993' and exists (select * from ...
11 votes
2 answers
3k views

How to determine cost/benefit of adding an index?

According to Craig Ringer: While it's usually a good idea to create an index on (or including) your referencing-side foreign key columns, it isn't required. Each index you add slows DML operations ...
0 votes
0 answers
10 views

How to optimise a Redshift table with a SUPER data type column?

We currently have a small table in Redshift with 3 columns col1 (id): varchar(36) col2: varchar(36) col3: SUPER Where the SUPER column contains a dictionary which can vary in length. The total size ...
0 votes
1 answer
34 views

Autovacuum does not clear the database

Autovacuum does not clear the database. Databases middle-db Postgres 10.18, AWS RDS(vCPU 2, RAM 8Gb, SSD(gp2) 1100Gib) Table "spree_datafeed_products" relid | 16556 schemaname ...
0 votes
1 answer
580 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 ...
0 votes
5 answers
130 views

MySQL's maximum memory usage is dangerously high

I'm trying to understand what is not working in my configuration. When I run the [root@server ~]# ./mysqltuner.pl >> MySQLTuner 1.9.8 * Jean-Marie Renouard <jmrenouard@gmail....
1 vote
1 answer
32 views

mysql optimization for intensive schema-altering queries (DDL)

In the context of CI would like to massively parallelise the running of tests. The code under testing require a database to be present, so MySQL is used (as the application goes with that right now). ...

1
2 3 4 5
38