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)

6
votes
2answers
454 views

MySQL: delete…where..in( ) vs delete..from..join, and locked tables on delete with subselect

Disclaimer: please excuse my lack of knowledge about database internals. Here it goes: We run an application (not written by us) which has a big performance problem in a periodic cleanup job in the ...
0
votes
0answers
84 views

Need your help in query tuning

I read ​one article which shows how to use different aggregations effectively. http://sqlblog.com/blogs/paul_white/archive/2011/12/04/is-distinct-aggregation-still-considered-harmful.aspx I am ...
2
votes
1answer
134 views

Select last row for a certain group

I have two tables: Table users columns | type | notes --------|--------|------- id | bigint | PK ... | ... | ... Table events columns | type | notes -----------|---------|-----...
1
vote
0answers
322 views

Why my sql query run faster than my stored procedure?

I am trying to write a script in MYSQL Workbench to update my database every day using stored procedure but I find it too slow compared normal query or temporary tables. If i use temporary table the ...
3
votes
1answer
1k views

Mongodb high lock percentage / slow queries

I'm working with an application using MongoDB that is getting a fairly large number of incoming requests and I've noticed that the responses are getting slower as the number of requests get higher. ...
1
vote
2answers
84 views

Is there's another function to optimize this query in MySQL?

I want to use a function that returns for me the same result in one line is that's possible?? MIN( CASE WHEN (data.pro LIKE '1 %' ) THEN 1 WHEN (data.pro LIKE '2 %' ) THEN 2 ...
2
votes
1answer
90 views

How to optimize this MySQL query?

Hi can someone help me to optimize this MySQL query, it's taking very long to complete, I have set indexes correctly, but it seems that string operations are slowing this query SELECT vendor_products....
1
vote
2answers
68 views

Is LIMIT processed before or after retrieving data?

MySQL. My heavy query crashed our MySQL server. If I add LIMIT 10 will it make my query more lightweight? (both for indexed and unindexed queries). Which way LIMIT are processed: before the rest of ...
3
votes
3answers
326 views

Optimizing a query which runs through all hours in a day

I need help optimizing a query. I'm using PostgreSQL 9.3.4 currently but can upgrade to 9.4 if needed. I have a table with 60+ millions of records which looks like this: ...
0
votes
0answers
35 views

Optimize query with access to tiny fraction of data

I am trying to optimize some queries but I can only use a tiny portion of the actual data they will be executed against later. Probably the query optimizer will generate completely different plans ...
1
vote
0answers
449 views

Oracle 10.2.0.4 query hundreds times slower than the same query on Oracle 10.2.0.3

I have an issue with query on Oracle 11.2.0.4.0 (it takes forever to complete and CPU is at 100% all the time) so I was trying to change optimizer_features_enable to previous versions. It worked with ...
1
vote
2answers
87 views

Optimize table by removing large field

I have a MySQL table of articles. It's growing rapidly, and over 50% of the size on disk is accounted for by the article body field, as you might expect. There are no indexes on the body, but 8/16 ...
0
votes
2answers
42 views

Which query performs better when searching a row where a column has maximum?

Consider a simple table for example purposes: person(name varchar, age int) I have a task to retrieve the oldest person. Which query is better in general for such problems? Query 1: criteria with ...
0
votes
1answer
39 views

optimizing mysql query using explain

I'm trying to optimize a query for a client; I'll try to describe what I'm going for at a high level before diving into the structure. The client database has a table of transactions (currently ~352k ...
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
75 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
312 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
99 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
4k 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
734 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
172 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
513 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
70 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
132 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
642 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
58 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
199 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
1k 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
61 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
224 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
71 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
341 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
224 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
93 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
710 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
256 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
53 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
27 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 ...