The Stack Overflow podcast is back! Listen to an interview with our new CEO.

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
1answer
30 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
30 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 ...
1
vote
1answer
37 views

how to override a table's cardinality or statistics histogram

I remember reading once that it is possible to force SQL Server to assume a table is a certain size, or perhaps even override a statistics histogram, when compiling a query. Can someone explain how ...
0
votes
1answer
30 views

Does “consolidation” of query statements improve query performance?

Assume we have a set of postal addresses A1, A2, A3, ... of the form: A1 = 124, "Smith", "Rd" A2 = 142, "Jones", "St" A3 = 8190, "Smith", "Ln" A4 = 142, "Via Gracia", NULL A5 = 190, "Jones", "St" A6 =...
2
votes
0answers
67 views

What's the more efficient way of selecting with multi left joins?

I have 4 tables, the first one is "student", the other 3 tables are different records of the student's activities. All tables have a id column as the primary key. student.id is varchar(64) (similar ...
1
vote
1answer
155 views

Optimize MySQL query and index for multiple column range query

I have a table like this: CREATE TABLE IF NOT EXISTS `jobs` ( `job_id` varchar(36) NOT NULL, `job_status` varchar(30) NOT NULL, `created_at` datetime NOT NULL, `lease_date` datetime, ...
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
1answer
555 views

Nested case statements vs multiple criteria case statements [closed]

Had an interesting discussion with a colleague today over optimizing case statements and whether it's better to leave a case statement which has overlapping criteria as individual when clauses, or ...
1
vote
1answer
55 views

How to automate basic ddl scripts execution and management [closed]

Looking for suggestions (A Script, third party tool, software etc.) which can help automate some of the administration tasks around execution of DDL scripts across environments. Database is usually ...
0
votes
0answers
20 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 ...
1
vote
0answers
60 views

How does mysql optimizer works?

I have two tables t_core_blueprints,t_ci. CREATE TABLE `t_core_blueprints` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `last_modified_on` ...
0
votes
1answer
46 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 ...
0
votes
1answer
45 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
189 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
32 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 ...
1
vote
1answer
27 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
33 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
71 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
231 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
44 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
27 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
159 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
61 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
vote
1answer
39 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
91 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
39 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
43 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
69 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
44 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
21 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
80 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
38 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
114 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
42 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
37 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
35 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
81 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
31 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
61 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
77 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
37 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
148 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
43 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
99 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
57 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
16 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
114 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
28 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
77 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],[...