Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [optimization]

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

0
votes
1answer
31 views

Oracle - large table with large IN

Table students has about 10 million records; ID is indexed. Student grades has 20 million records - student_id is indexed. I am querying about 20,000 students by their ids ids with a join between ...
-3
votes
0answers
25 views

Open source database for testing algorithm [on hold]

I have developed algorithm which probably would optimize query executions and accuracy of results. I am seeking for some open source or free database systems where I could run this algorithm. Even ...
0
votes
1answer
28 views

Speed up slow postgres query with indices (~5 minutes)

I have roughly 100 million rows in a table called 'bets' and a couple million in 'games'. The problem is that even with indices particular queries run very slow. The tables are structured as so: ...
2
votes
2answers
150 views

Should I split timestamp parts into separate columns?

I am building a PostgreSQL database and I have created a timestamp table, where the primary key is the timestamp itself (e.g. id: Fri Apr 13 2018 15:00:19). The database is supposed to be later ...
0
votes
1answer
22 views

Analyzing a query plan for a query with TWO predicates, but index only has ONE

Note: This is from an introductory database systems course at my university so I'm not entirely sure if it matters, but for what it's worth we are being taught MySQL. One homework question I'm doing ...
0
votes
0answers
29 views

Optimize a query with unexpected results [closed]

I don't post in this SE often, so if I'm not explaining the query properly please let me know and I will edit. I have to obfuscate some of the work that I am doing so I will try to explain my issue ...
1
vote
1answer
26 views

Mysql Query Optimisation For Joining a BETWEEN Date and Date query

I have some instagram API data sitting across two tables. One with post_data, and one with profile_data. The profile_data table has a timestamp of when the account was checked through the API to get ...
0
votes
1answer
18 views

mysql 5.6 is Not referring Indexes for deterministic function DATEDIFF

I do not understand why MySQL is not using the index for the below query. I have a table named users and its create table is this table contains data. Now I am running SQL select statements and it'...
0
votes
1answer
63 views

How to optimize a SQL Server query to fetch the results faster

Currently I am having a situation where I have to import around 0.5 Million rows into a SQL Server table named BARCODE_MANAGEMENT, which has the following columns: ID int (identity column), SETUP_ID ...
3
votes
3answers
208 views

Query to categorize rows based on a “time” column without using a CASE expression

There is a ProductTT table as you can see below: [dbo].[ProductTT] (ID int , Product Varchar(50) , Time Int) ...which contains the following rows: 1 XX 0030 2 UY 0354 3 YY 0517 4 ZZ ...
1
vote
3answers
42 views

Detect one-sided matches

I have a SQL Server 2014 database table containing a code and two identifiers: DECLARE @Foo TABLE (code CHAR, id_a INT, id_b INT) INSERT INTO @Foo (code, id_a, id_b) VALUES ('A', 1, 2), ('B', 2, 1), (...
0
votes
0answers
38 views

SQL Server query auditing in production without drastically affecting performance

Has anyone successfully turned on SQL Server auditing (extended events) to keep track of queries being run in a production environment while having the database still perform well? If you did, I'm ...
0
votes
0answers
25 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 | ...
3
votes
2answers
58 views

updating statistics using STATS_STREAM or WITH FULLSCAN?

the syntax for creating statistics is: -- Syntax for SQL Server and Azure SQL Database UPDATE STATISTICS table_or_indexed_view_name [ { { index_or_statistics__name ...
1
vote
0answers
57 views

Slow ORDER BY with large and small tables

I have one small table (200 rows) and two large tables (200k rows each). Small table has primary key id and some other fields. First big table has id field (not a primary key), which are duplicated, ...
-1
votes
0answers
39 views

Optimize MySql Self Cross Join and Grouping - Preorder Tree Traversal Algorithm

I'm on MySql 8, I am trying to implement Preorder Tree Traversal (Referal : https://gist.github.com/tmilos/f2f999b5839e2d42d751) I have 65,000 rows which i traverse, the following T-SQL takes ...
1
vote
1answer
36 views

Optimize MySQL InnoDB query for SUM and Order By

I have an MySQL InnoDB table with 1.3M rows and 670MB size: +----------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------...
1
vote
1answer
50 views

Why are some count queries so slow?

I have a table users with 2.6 millions of rows and SELECT COUNT(*) FROM users takes 2 seconds to succeed, but request for table stories with 5.2 millions of rows takes more than an hour. Explains are ...
0
votes
0answers
34 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 ...
3
votes
1answer
35 views

MariaDB configuration for many updates, writes

Based on few tutorials that I show I created the following MariaDB configuration for my server. Server has 32 GB ram, SSD, 8 cores and is dedicated to run MySQL. The biggest table has about 1,5 ...
1
vote
2answers
65 views

MySQL: need help making a query faster

I wrote a query that finds me records in discovery table that contain a needle from whitelisted (LIKE %needle%) and don't have a record in logs table (and some other easy to understand filtering): ...
0
votes
1answer
40 views

Optimizing MYSQL statements

Recently, I started working on a project, where I came across many MySQL statements like this: SELECT s.Important, c.Interesting FROM table1 s, table2 c WHERE s.IdForTableC = c.id or SELECT q....
0
votes
1answer
20 views

Query optimization: how to efficiently occlude overridden records?

Imagine a table with roughly a couple of billion rows. A simplified schema for the table would be started_on: datetime recorded_on: datetime image_id: integer It's indexed by started_on. One ...
1
vote
1answer
76 views

Redundant fields in ORDER BY for efficient sorting?

Suppose I have a table SortTest with fields Data1, Data2, Sort1, and Sort2, Key1 and Key2 need to perform the following query: SELECT TOP 1 Data1, Data2 FROM SortTest WHERE Key1 = @key1 AND ...
0
votes
2answers
27 views

Cannot figure out how to remove this Using Temporary

I have what seems like a simple query, and my indexes seem to be sufficient without the INNER JOIN, but after I add the join, I end up seeing "Using Temporary". Would also appreciate any good ...
3
votes
1answer
102 views

Direct values on predicate produce not-so-good plan

I'm using StackOverflow dump to run some tests. In particular, I'm querying this table: I have created this index: I'm running the following query (just forcing an index to test alternatives) I ...
1
vote
0answers
40 views

MySQL Query Optimization Group By with Max [closed]

I have posted same question on at stackoverflow.com but I did not get any structural solution so I am trying to get answer on dba specific community. Trying to optimise MySQL query which uses Group ...
0
votes
1answer
35 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
32 views

What is the best compaction strategy for overwritable/mutable/upsert-able data?

Suppose I have a Cassandra table like this: CREATE TABLE keyspace.last_modified ( category text, subcategory text, id text, created_at bigint, PRIMARY KEY ((category, subcategory, ...
1
vote
2answers
66 views

Need suggestions in improving my.cnf configuration

i need some suggestions in improving MySQL performance, i didn't find any issues in general but i feel MySQL is taking more CPU than before. Below is the output from mysqltuner.pl >> ...
0
votes
1answer
28 views

Union of two queries taking 2.4 seconds I need it in ms

I need to fetch data from two different sources one is filtered by geography column and second is just simple table with few joins, this is a response to an api call which should be fast enough for ...
2
votes
2answers
47 views

What is better - having 10 databases for 10 organisations or 1 table for 10 organisation with org_id? [duplicate]

I am making a web service (using Laravel in case that is relevant) that will have many separate organisations generated by the service. Considerations I can manage all organisation data in one ...
0
votes
0answers
62 views

query increase performance based on condition on another tables in sql server

I have a shown part of the database that is relevant to the question in the images below. The problems am having table TestMArk has big data for about 10 years with about millions rows for schools ...
0
votes
1answer
34 views

PostgreSQL poor performing order_by ASC

I have a situation where I prepare a list of buffers to process by a task queue. Currently my buffer table contains over 350.000 rows. Every estimated 30 seconds the following query is ran to check ...
0
votes
3answers
92 views

MySQL Single Left Join Very Slow

SELECT contacts.email, emails_stats.id FROM contacts LEFT JOIN emails_stats ON contacts.id = emails_stats.contact_id WHERE contacts.client_code = 121212121212 GROUP BY contacts.email; ...
0
votes
0answers
33 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 ...
2
votes
1answer
94 views

Indexing strategy for a query involving three views, multiple columns and several JOINs

The scenario I am having issues with a SELECT query on a view named V3, which takes ages (I had to cancel in SSMS, it timeouts in the application). V3 combines two other views, V1 and V2, to get a ...
0
votes
2answers
38 views

How to optimize SQL query which involves GROUP_CONCAT function with ORDER BY

I wrote the following SQL query to group results and then sort results in each group by date: SELECT origin, provider, SUBSTRING_INDEX(GROUP_CONCAT(price ORDER BY departure_date ...
-2
votes
1answer
38 views

How would you do this simple query in the most optimized way MYSQL and also MARIADB

pkid clientname location 1 Jones NY 2 Jones NJ 3 Jones TX 4 Jones FL 5 Jones IA 6 Jones NC 7 Jones SC 8 Jones VA 9 Smith FL 10 Smith CO ...
0
votes
0answers
13 views

Join hive tables which are being populated in real time

I have two tables in hive which are being populated in real time from a set of files. There is a request_time and trx_id columns in both tables where trx_id is the joining key. I want to join them ...
1
vote
1answer
59 views

SQL Server Query Store - Force Plan does not work

Here is my situation: I have a simple SP (containing giant joins and a few temp tables), which essentially returns a resultset, works in 15 seconds in 2008 compatibility level, but takes 3 hours when ...
0
votes
0answers
17 views

Postgis KNN in Postgres 9.5

I'm trying to use the Postgis KNN functionality as described here: https://postgis.net/docs/geometry_distance_knn.html I have one table with all point geometries. The table has many samples, and in ...
2
votes
1answer
59 views

Calculate difference between current value and next value without CTE/ inline table

I have a table that has userid and his visit dates. I have created a CTE with adding a next visit column and using this to calculate day difference between visits. With Visits AS ( SELECT [uid],[...
0
votes
0answers
40 views

Optimise multiple inner JOIN

Consider the following query: mysql> SELECT GROUP_CONCAT(a.id SEPARATOR '\n') -> FROM assignment a -> INNER JOIN statements s ON a.id = s.assignment_id -> INNER JOIN ...
4
votes
1answer
161 views

Adding an additional “order by” column gives me a much worse plan

in other words, how can I get rid of the sort operator on the picture below? the picture above shows the execution plan of the following 2 selects together: SELECT TOP 1 so.OrgType,...
0
votes
0answers
20 views

How to optimize a query with select, row number and a lot of LIKE

I try to find search rank of a product in today's product history records. For this I use 6 ILIKE and 2 inner join. I have indexed all columns but still so slow. It takes approximately 10 sec. to ...
1
vote
0answers
231 views

What is the best option for a large slowly changing dimension lookup table

This question is an extension of a previously asked question Index Strategy and Query optimization, which further extends this question: Search between IP string addresses I implemented the two new ...
1
vote
2answers
47 views

What is the impact on the Cost-Based Optimizer (CBO) when implementing ISCSI SAN solid-state drives

A Microsoft Technet article recommends creating a secondary file group as the default file group (see reference below). The secondary file group should have a number of files, say four, that are each ...
7
votes
3answers
120 views

SQL Server creates different plans when OR condition is re-arranged

I was reviewing an under-performing query which looks like this: WHERE manymany.Active = -1 AND manymany.Check1 = -1 AND manymany.WebsiteID = @P1 AND CURRENT_TIMESTAMP BETWEEN ISNULL(manymany....
0
votes
1answer
27 views

Filter predicates executing before Access predicates

This is oracle 11g. When I try to do a select query on a table, in the explain plan option I see that the filter predicates of the query(predicates on column without indexes) are executed before the ...