Questions tagged [optimization]

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

367 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
1
vote
0answers
360 views

Mysql key_buffer_size already 16GB but some fulltext searches are still slow

I have a query w/ full text search which takes 5 - 10 secs. key_buffer_size is already 16GB. Table has 15 million records and has a compound fulltext index on fields: categories and company. Table ...
1
vote
0answers
4k views

Using something better than UNION for combining multiple queries

I have one big query which is based on 12 smaller queries that I have put together using UNION. A relative simple example of my query: (SELECT 'type1' AS 'type', fieldOne AS someField, ...
1
vote
0answers
2k views

How can I join one to one to (many to one) without using group_concat

I have a table structure as follows: -- -- Table structure for table `emails` -- CREATE TABLE IF NOT EXISTS `emails` ( `ID` int(5) NOT NULL AUTO_INCREMENT, `email` varchar(255) COLLATE ...
1
vote
0answers
749 views

Finding the bottleneck by analyzing Munin graphs

We’ve been experiencing some issues with our database server during peak hours. In our Nginx error-log we can see that a lot of requests get timed out. The requests are also very slow. We have had ...
1
vote
2answers
52 views

Best practice for “recycling” rows on PostgreSQL (UPDATE vs INSERT + DELETE)

I searched responses on "UPDATE vs INSERT + DELETE" in general terms and the consensus is UPDATE is faster, as expected. My question is centered on a somewhat niche use case. Let's say you ...
1
vote
1answer
104 views

optimizing query for slow queries

I have this query in my slowqueries table which essentially is taking about 5-8 seconds to complete the query time: SELECT s0_.id AS id0, s0_.updated AS updated1, s0_.created AS created2, ...
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
0answers
85 views

Improving the run time of a DW query

I have a user running query which is sometimes under 10 seconds while other time going over 30 minutes. This is not a store proc , he is doing it manually from his machine to get data and later after ...
0
votes
1answer
23 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 ...
0
votes
2answers
65 views

Asking Suggestions regarding mysql optimization

Im running a dedicated server with around 20-25 sites, almost all of them running wordpress installations. Running it over a cpanel setup. For a while its mostly mysql eating most of the cpu and ...
0
votes
0answers
20 views

UPDATE vs SELECT returns different Execution Plan

In my DB table, where ID is the PK, if I use this: EXPLAIN SELECT * FROM table WHERE ID = 3; The type and the ref columns of the explain output table are both equal to const. Now, when I use this: ...
0
votes
2answers
41 views

Optimize duplicated but negated subquery

I have what I call a 'Hamlet' query in the form: select * from stuff a left join otherStuff b on (a.x=b.x) -- lots of joins ommited where (b.ToBe = 1 and a.Being in (select Being from stuffThatIs ...
0
votes
1answer
73 views

Decrease Data IO operation on query to lower the DTU cost on Azure

We have a FunctionApp in Azure that triggers on a timer and does some DB operations every 5 minutes. These operation are taking a very long time to complete. When I run the same query on my computer ...
0
votes
0answers
44 views

SQL Server Persisted Computed Columns, Issues during Mass Insert or Update?

I have a database with a computed, persisted column which validates columns, checks if its an integer, and converts. Sometimes streetnumber is alphanumeric, like 5C, other times it's simply an integer....
0
votes
1answer
19 views

Which SQL table statistics are used after they are updated when NORECOMPUTE is turned on?

Background: I have a large table whose values are significantly rewritten with a switch ~once a week, but the table's primary key values remain relatively constant. After the switch, statistics are ...
0
votes
0answers
17 views

High-performance query of two tables with count from second table?

I am searching for a high-performance query using two tables to output all locations (name) from country (adm0 = 'DE') in the geodb_locations table, sorted in descending order by the number of users ...
0
votes
1answer
38 views

Optimising MySQL count() with INNER JOIN (550k rows)

I have the query below, which is currently taking approx 1 - 1.5 seconds to run in a MySQL 5.7 database. It's being called from a web application, and whilst I'll be implementing caching at the ...
0
votes
0answers
48 views

How do I debug an “Operator used tempDB to spill” warning when statistics appear to be ok

I have a section of a complex query plan where a Sort operator appears directly after an Index Seek and SSMS shows a "Operator used tempDB to spill" warning. It is reported to be taking 70% ...
0
votes
0answers
17 views

Optimal selection of predefined non-materialised data

Take the following result set as an example: Store Category Item Cost Stock Sold Ordered ABC Accessories Lock 344.31 281 141 225 DEF ...
0
votes
1answer
190 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 +----------------------...
0
votes
0answers
47 views

PostgreSQL efficient lookup for 2 column combinations

I have a PostgreSQL (v. 12) database storing match results. I have integer columns team_home and team_away which have foreign key constraints for team_id in table teams . My question has a few ...
0
votes
0answers
39 views

MySql Group By Query Taking More 5 seconds On An Indexed Table

I have a simple analytics table which has a little over 2 Million rows and currently growing at a pace of 20k rows per day. Please check the table CREATE TABLE IF NOT EXISTS `affiliate_analytics` ( `...
0
votes
2answers
57 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
0answers
9 views

Sql combination matching criteria - The icrecream kiosk issue

Let's create this case Icecream type | Available |Normally eats (person) | Price Small cup 2 1 1.50 Medium cup 0 1.5 2.00 ...
0
votes
0answers
21 views

Which aspects of PostgreSQL `UPDATE` do impact IOPS?

Premises: there is a big table (hundreds of millions of rows), a new column is added to the table and needs to be populated with values. UPDATE by chunks (e.g. id ranges of different sizes) causes ...
0
votes
0answers
27 views

Multiple join criteria with different keys?

I am having a hard time formulating this question for a targeted search and so none of the results I am getting seem to describe my exact situation. I work in healthcare and our tables are enormous, ...
0
votes
0answers
57 views

“INNER JOIN” vs “INTERSECT” performance comparison when dealing with huge amounts of data in Oracle

I have two tables with the structure below: create table Table_One (Ef_date Date, Customer_Num Number, <Other_Columns> <Combination of Number/varchar2(50)>); create ...
0
votes
1answer
209 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
2answers
328 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
20 views

Triggers or views on frequently updating table

We have a table which is frequently updating. We have a scheduler to update this table which runs every 10 minutes. There are not more than 50k records in this table but when scheduler runs, database ...
0
votes
1answer
33 views

Join on large table Myisam & Mysql 5.0

I searched on some forums, perconna and more but i didn't find answers. We have a legacy server with Mysql 5.0 and some tables on Myisam. I'm trying to optimize my request. We have 3 tables mysql&...
0
votes
0answers
21 views

Save multiple dynamic filter for aggregation in Postgresql

What is the most efficient way to store multiple dynamic filters which can be created by users on given data and us the filter for aggregating data? Lets assume I have a table called t_filter which ...
0
votes
1answer
30 views

Querying an enormous table (~150 million rows) with multiple joins - SQL Server

First off, I don't have SHOWPLAN permission on this server or permission to add indexes, etc. I am not a database administrator by trade; I'm just trying to query a database. There is already indexes ...
0
votes
0answers
17 views

Condition filtering estimate calculating double dipped extra value without performing range reduction

In a query like below select e.emp_no, concat(e.first_name, ' ', e.last_name), d.dept_name, t.title_name, es.salary, es.insurance, es.pf, ea.city, ea.state, ea.phone from ...
0
votes
1answer
40 views

Index dive/sampling row count very inaccurate in MySQL

The table emp_address_phone is filled with many random values. CREATE TABLE `emp_address_phone` ( `address_no` int(11) NOT NULL, `emp_no` int(11) DEFAULT NULL, `street1` varchar(255), `...
0
votes
1answer
33 views

problem with optimizing SQL query

I'm noob when it's come to DBMS so i wrote a query on couple of relations and i want to know what's the best way to do it because i don't feel good about my query. Here are the relations: CREATE ...
0
votes
0answers
61 views

SQL Server Full-Text Search Order by another Column Performance

I have two tables, Entity and EntityDetails. Entity table has columns EntityId, Col1, Col2, Col3, LastUpdated (smalldatetime). EntityDetails table has columns EntityId, Details1, Details2. Where ...
0
votes
0answers
50 views

How to count references to fields given the definitions of views?

I have a bootstrapping script which defines views to be exposed to the data users in public schema. It filters out sensitive fields from the tables in private schema, also makes various aggregations. ...
0
votes
1answer
43 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
0answers
18 views

do statistics need to be calculated each time or stored?

I have a "user" table in a video-game, which have these properties: Money Experience Number of friends Each of these properties can be either stored in the table, and updated when something happen (...
0
votes
1answer
131 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
1answer
29 views

Optimize query with a subquery (index is not used)

I have following query: SELECT `status`, `is_client_coordinator`, id, first_name, last_name FROM `users` `t` WHERE t.status!="CLOSED" and t.is_client_coordinator or t.id in (select ...
0
votes
0answers
35 views

Optimise the selection of two distinct column pairs from the same table

I need help with rewriting or editing the query/logic to improve it's performance. SQL Server 2014 I have the table structure specified below. I want to select two column pairs: (SourceID + ...
0
votes
0answers
59 views

Calculating historical data over date ranges for up to 1 million rows

I need to be able to calculate the average sales of employees over a period of time. At the moment, I am doing this by using MySQL to get the total sales per employee per day: SELECT ANY_VALUE(...
0
votes
0answers
52 views

Is there a way to optimize the query if we are using ROUND function 100 times in a single query

The below query is to compare the last day of the month and select either zero or a value. I am not pasting the entire query as its huge and the i am just pasting the condition which if possible can ...
0
votes
0answers
13 views

When will 'Max_execution_time_set_failed' status variable be incremented in Mysql?

Server: Mysql 5.7 By documentation, it has been said that The number of SELECT statements for which the attempt to set an execution timeout failed. Can anyone explain the scenarios or share ...
0
votes
0answers
9 views

High cost on my query on partitioned table

I have a partitioned table as follows: create table daily_summary ( id uuid default uuid_generate_v4() not null constraint daily_summary_partition_pkey ...
0
votes
1answer
126 views

MYSQL Select query stuck at “Sending data”

I have an select query with about 1M records, I'm working on Magento 1.9 database. SELECT IF(sup_ap.is_percent = 1, TRUNCATE(mt.value + (mt.value * sup_ap.pricing_value / 100), 4), mt....
0
votes
2answers
67 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, ...
0
votes
0answers
189 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 ...

1 2 3
4
5
8