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

learn more… | top users | synonyms (3)

1
vote
4answers
1k views

MSSQL Select all parent rows for which exists a combination of children rows with desired value

I would like to select all parent rows for which exists a combination of children rows with desired value. The easiest approach is to use multiple exists clauses, but this is not optimal - anyone ...
1
vote
1answer
73 views

Using NOCOUNT to Improve Procedure Performance

I am looking to improve the performance of a certain procedure and I wanted to begin with the insertion of the SET NOCOUNT ON. I've read a couple of articles on the subject: Aaron Bertrand SET ...
2
votes
2answers
309 views

MySQL JOIN two tables and get latest result

I want to JOIN two tables and get the latest result from each one of those two in a single table. I'm kind of concerned about speed too since tables are growing kinda fast. Close to 60-70k records a ...
1
vote
1answer
98 views

Optimizing a query that performs worse on a smaller dataset

I am trying to run this query. The * is just for simplification, I'm selecting ~10 specific fields for some calculations. SELECT * FROM `orders` LEFT JOIN `orders_processed` ON `orders_processed`....
0
votes
0answers
29 views

Filesort when using view

I am sorry for asking, but I can't figure what i am doing wrong and filesort appears in my explain. The id column that I use for sorting in the following examples is a primary key... If I create view:...
14
votes
2answers
3k views

Trace Flag 4199 - Enable globally?

This may fall under the category of opinion, but I'm curious if people are using trace flag 4199 as a startup parameter for SQL Server. For those that have used it, under what circumstances did you ...
2
votes
2answers
690 views

Slow Performance caused by Correlated Sub-queries & Clustered Index Seek

I'm running a query in which one of the tables (FIELDDATA) has nearly 400,000 records which is taking more than 2 minutes to execute. I ran the execution plan, and noticed that the clustered seek ...
1
vote
1answer
134 views

Generate rows based on value in 2 columns. Bug?

I have the following table in Oracle: ID START END _ID01 AB050 AB100 _ID01 AB101 AB150 _ID02 AB0251 AB0300 _ID01 AB00301 AB00302 I want to generate as many rows and every ...
3
votes
3answers
166 views

Why does the CBO eliminate `distinct` and not `group by` in this query?

The CBO chooses to eliminate the distinct in the 'slow' query - presumably it can tell that the operation isn't needed because of the outer group by. My questions are: 1) Why does it choose to do so ...
0
votes
2answers
481 views

Sqlite query slow whilst it should be fast

I have the following query: SELECT breakType,date,blockname,modname,metadata,nbtdata,x,y,z FROM BlocksBrokenByExplosion WHERE dimension=0 AND y>60 AND y<70 AND x>150 AND ...
0
votes
1answer
69 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 ...
6
votes
2answers
105 views

Will the Oracle optimizer still plan a join if I'm not reading any columns in the joined table?

Suppose I have the following view: CREATE VIEW dummy AS SELECT a.some_column, b.other_column FROM a LEFT JOIN b USING (key_column); When I do a SELECT some_column FROM dummy, is there a way to ...
0
votes
3answers
128 views

Adding order by to a query is slowing down the query even after Indexing

While joining columns with huge data even after indexing the column order by is slowing down the query. Without order by query is executing within 2 secs. But with order by query is taking 145 secs. ...
2
votes
2answers
1k views

SELECT and UPDATE in a single query while updating selective fields of duplicate records only

I have a MySQL table named Contacts_table with, say, the following contents: ID NUMBER NAME CITY 001 2222222222 John Doe Los Angeles 002 ...
0
votes
1answer
51 views

How to determine if an Index is uselss in Percona MySQL?

Using Percona Server 5.5 I've a table which stores user activty (lets call it user_activity). It was created quite a few years ago when I had only a little bit of knowledge about database ...
3
votes
2answers
116 views

Mysql Index for concat select

For Below Query SELECT MAX(CONCAT(date, ' ', last_entry)) AS LAST_LOG FROM entry_log WHERE TRIM(LEADING 0 FROM card_no)='2948' I've index on date card_no date,last_entry date,last_entry,...
4
votes
2answers
610 views

Optimizing queries on a range of timestamps (one column)

I am using Postgres 9.3 through Heroku. I have a table, "traffic", with 1M+ records that has many inserts and updates every day. I need to perform SUM operations across this table over different time ...
0
votes
2answers
57 views

why the optimizer doesn't choose the optimal plan [closed]

I know, if the statistic is not updatad, it is possible, that the optimizer doesn't choose the optimal execution plan? Does any other reason/reasons exist?
5
votes
1answer
185 views

Query looks optimal but logical reads is huge

I run the following (really simple) query in SQL Server: SELECT MAX(PK_Field1) FROM MainTable kh WHERE kh.PK_Field1 >= '2014-12-01T00:00:00' AND kh.PK_Field2 = 1572 AND kh....
3
votes
1answer
935 views

DB2 CASE statement faster with parentheses around entire WHEN

Trying to optimize slow queries, someone on my team come across this. Having this in our stored proc: CASE WHEN MYCOL BETWEEN @STARTNUM AND @ENDNUM AND RECTYPE = 'RO' THEN 1 ELSE 0 ...
0
votes
1answer
59 views

Storing small array of many possibilities

Let's say I need to store the text of a series of books, line per line in a database, and present them in a (CMS-based) website. I thought the correct setup would be: a 'library' table: containing ...
0
votes
2answers
213 views

How to most efficiently store possible combinations of categories?

I have a table of categories (currently with 14 rows, not likely to change). A need to cache combinations of categories emerged, so I'm creating a table of allowed combinations (only few hundreds of ...
1
vote
0answers
66 views

MySQL Cluster 7.4.6 - Analyze table failure on 15m reference table

I'm running MySQL Cluster 7.4.6 on Azure VM's with the following infrastructure: 2 x Mgmt Nodes: Ubuntu 14.10LTS, 2 cores, 3.5GB ram 2 x MySQL API Nodes: Ubuntu 14.10LTS, 2 cores, 3.5GB ram 2 x Data ...
2
votes
2answers
338 views

MySQL taking too much CPU

Below is my .cnf file and if I see my CPU consumption 99.99% of it my the mysqld commands. The MySQL server is connected from remote machines that update data in it frequently, but I make sure that ...
1
vote
0answers
214 views

Optimizing a geo search in Postgres

I'm searching for UK postcodes within a radius of a given postcode. The query that I'm using looks like this: SELECT *, earth_distance(ll_to_earth( {$lat}, {$lon} ), ll_to_earth(lat, lng)) as ...
2
votes
1answer
92 views

Can this UPDATE be faster with indexes?

update line_items set product_id = dups.latest_product_id from line_items as li join products p on li.product_id = p.id join vendors v on p.vendor_id = v.id join vendorgroups vg on v....
2
votes
1answer
631 views

Oracle 11g hash join vs nested loops question

I'm using Oracle 11g and working on a legacy system. I have two tables, A and B. A has ~40M rows and B ~4M rows. A has a foreign key on B (B is the parent of A). A query does essentially this: ...
0
votes
1answer
247 views

Optimization for aggregation and grouping with MySQL

I have an application that is pulling data on a daily basis from numerous APIs, storing it and then presenting it on the front end in various charts, tables, etc.. I'm having some issues avoiding ...
0
votes
1answer
52 views

MySQL 2 Million Rows, longtext and varchar indexes

I'm trying to find a solution for this, with no success. I have a table with ~2M rows, and I need to do a WHERE with 3 expressions using OR. name LIKE '%PARAM%' OR text1 LIKE '%PARAM%' OR text2 LIKE ...
0
votes
1answer
90 views

Rolling update to all rows in innodb table (no rollback if aborted)

I have the following table, which stores the Social Security Death Master File database (~86M rows): CREATE TABLE `death_master_files` ( `id` int(11) NOT NULL AUTO_INCREMENT, `ssn_an` smallint(5) ...
1
vote
0answers
26 views

Optimizing MySQL for write throughput ceiling

We have a large MySQL database on AWS RDS, with a huge table around ~1.8TB with massive write workload. We recently hit a write throughput bottleneck, which caps our provisioned IOPS way below the ...
1
vote
1answer
39 views

Reducing query cache for mostly INSERT-heavy database?

What sort of MySQL query cache should I choose considering the following... Large, rapidly growing database (I got 500,000 rows). UPDATEs practically never happen. SELECTs are rare, but when they do ...
0
votes
0answers
186 views

Optimizing a CTE query - overhead in inserting into a new table from select statement

I have two tables with the same structure like so (let us call them initial_table1 and initial_table2): tempid name num_records 1 a 2 2 b 2 3 c 2 4 d ...
0
votes
3answers
361 views

MySQL - How do I Calculate an Average Value Over a Period of Time When Only Value Changes are Recorded

I am tracking the sales price of various items, and I would like to calculate the average price of each item over a period of time. My simplified tables look like this: Items ------------------ |key ...
0
votes
2answers
73 views

Oracle query optimization

We have a query that runs on a view and its execution time is about 6 min. SELECT COUNT(*) FROM someschema.item_total_violations WHERE channel_id = 122507833 AND item_date >= TIMESTAMP'2015-02-...
0
votes
1answer
370 views

Is it possible to simplify a query with multiple joins to the same table?

I have a rather large query which takes longer to run than I would like because of 17 LEFT JOINs on the same table. I've done some searching, but haven't found anything that is really similar to my ...
8
votes
1answer
811 views

How does SQL Server's optimizer estimate the number of rows in a joined table?

I am running this query in the AdventureWorks2012 database: SELECT s.SalesOrderID, d.CarrierTrackingNumber, d.ProductID, d.OrderQty FROM Sales.SalesOrderHeader s JOIN Sales....
0
votes
1answer
74 views

MYSQL any way to improve this query's performance?

MYSQL version 5.6.23 CENTOS 7 Hi, I have the following query that is coming from an application that I am hoping to make more efficient from the mysql side of things. I have an index built on ...
0
votes
0answers
41 views

Best way to run a formula (segment) a large number of users with custom attributes

Here is my question. I have a table that stores a large number of people (80 mil). Each person has a GUID id. Then I have another table (or set of tables) that stores information about people. e.g. ...
0
votes
1answer
190 views

Get the latest record and count them with time interval

I have a table as below id fk_sales_id status created_at 1 001 5 2015-02-17 16:45:44 2 001 8 2015-02-18 18:45:44 3 002 30 2015-02-20 16:45:44 ...
0
votes
0answers
31 views

Subquery optimization

I was reading through this article - http://explainextended.com/2009/09/18/not-in-vs-not-exists-vs-left-join-is-null-mysql/ I am working with an existing design. What I want is to be able to select ...
0
votes
1answer
27 views

Optimize Query in MySQL

I've following tables with respective fields 1) wi_individual_g(ind_id,ind_is_recepient,ind_deleted,ind_district_id,...) - Around 200K data 2) wi_individual_p(ind_id,prg_id,...) Around 250K data 3) ...
5
votes
1answer
168 views

Is it possible to get seek based parallel plan for distinct/group by?

An example from this question shows that SQL Server will choose a full index scan to solve a query like this: select distinct [typeName] from [types] Where [typeName] has a non-clustered, non-...
1
vote
0answers
62 views

Is there a way to improve this query further?

I've got a query that over time I've been improving. Originally it simply selected * from the records table, and the data was formatted on the webapp side. But that was slow and I wanted to make ...
2
votes
2answers
280 views

How to handle too many inserts?

I have a database that is accessed from remote locations in the world with very long delays and poor transfer speeds. I currently transfer the data using a transaction since either all or no data ...
0
votes
0answers
43 views

Oracle query optimization time

Is there any way to get how long it takes the optimizer to optimize a query in Oracle?
2
votes
0answers
69 views

Performance differs on same query against two (almost) identical tables?

I'm running these two queries in SQL Server 2012: SELECT tpua.Field1, CAST(round(twg.Field2 * 2, 0) / 2 As Decimal(8,1)) As Field2, CAST((round((twg.Field3/10.0) * 2, 0) / 2) * 10 As int) As ...
1
vote
1answer
59 views

Is there any way to trace the optimizer's work in Oracle?

Just like the MEMO structure in SQL Server which is kind of a "paper trail" of steps the optimizer takes in optimizing the query, is there anything in Oracle through which I can see what are the ...
1
vote
1answer
59 views

Is there any way to trace optimizer's work in DB2?

Just like the MEMO structure in SQL Server which is kind of a "paper trail" of steps the optimizer takes in optimizing the query, is there anything in DB2 through which I can get the information like, ...
0
votes
1answer
86 views

Replace nested “select with count” with outer join? is this possible?

SELECT c.classid , c.classname , (select count(s.id) from students s where s.classid = c.classid and status = 'failed') as noOfFailed, (select count(s.id) from students s where s....