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)

4
votes
1answer
117 views

How to get the information of the optimizer of sql server?

The info like total optimization time, the time spent on each stage of optimization, the number of alternative plans and the cost of each alternative plan. Is there any way to get these info using sql ...
4
votes
1answer
302 views

Optimizing MySQL -

I've been researching how to optimize MySQL a bit, but I still have a few questions. MySQL Primer Results http://pastie.org/private/lzjukl8wacxfjbjhge6vw Based on this, the first problem seems to be ...
4
votes
1answer
284 views

MySQL: A better way to write this query?

We are using the following query in our system: SELECT f.type AS type, COUNT(i.id) AS submissions, SUM((CASE (e.oiPost + e.oiPhone + e.oiEmail) WHEN 0 THEN 0 WHEN ...
4
votes
1answer
2k views

How to use the METHOD_OPT parameter in DBMS_STATS.gather_table_stats

Hi there I was just wondering what would be the difference between these 2 METHOD_OPT parameter settings: FOR COLUMNS SIZE 5 some_column and FOR COLUMNS some_column SIZE 5 both will create a ...
4
votes
1answer
2k views

Why does MySQL use all of memory and goes into swap when doing lots of delayed inserts?

We have a database server running Centos 5.6 64bit 64Gb of RAM. The server is idle most of the time. However, every hour it gets a lot of DELAYED inserts. Up to 3 mil INSERTs per hour into a MyISAM ...
4
votes
1answer
225 views

SQL query filled Temdb complete Harddisk [closed]

I have a query which was earlier fine but now it is running me out of hard disk space with message that tempdb is full. My Harddisk has 90 gb of space as soon as i run query temdb runs out of space in ...
3
votes
1answer
484 views

Improving select query

I was at an interview for a new job today and got asked a question regarding a SELECT query: SELECT lastname FROM users WHERE name='John'; This SELECT query is to run over a million rows. I was ...
3
votes
2answers
241 views

SQL server tools to identify query optimisations and missing indexes [closed]

I have a broad question regarding SQL Server that was given to me on a quiz and I am not sure what the answer is as I do not have access to SQL Server at home. My question is how can I utilise SQL ...
3
votes
2answers
4k views

Updating a table efficiently using JOIN

I have a table that has the details of households and another that has the details of all the persons associated with the households. For the household table I have a primary key defined using two ...
3
votes
2answers
1k views

Optimizing a Postgres query with a large IN

This query gets a list of posts created by people you follow. You can follow an unlimited number of people, but most people follow < 1000 others. With this style of query, the obvious optimization ...
3
votes
2answers
778 views

SQL Server Baseline and Performance Optimization

I've been struggling a little bit around Performance Troubleshooting including baseline and troubleshooting of SQL Performance. Could anyone help with this or point me to where I can possibly get a ...
3
votes
2answers
95 views

How does TOPN influence a query with group by?

I have a query that looks like the following: Select t1.field1, t2.field2, sum(t1.field3) From t1 inner join t2 on t1.id = t2.id Group by t1.field1, t2.field2 It's simplified, normally there are a ...
3
votes
1answer
123 views

Does combining columns into 1 column helps to optimize a large table?

I have user, items and user_items tables, and as usual user has many items. Tables contains: User: ~50K Items: ~3.5M UserItems: ~5M I am using MySQL and most of my queries are selecting items of a ...
3
votes
3answers
312 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: ...
3
votes
2answers
961 views

What are the options of reducing tempdb latency?

Took the job of monitoring and tununing a SQL Servers 2012 Enterprise Ed. SP1 (64 bit) on Windows Server 2008 R2 Standard Ed. All of them are running inside virtual machines. One SQL Server per a ...
3
votes
1answer
9k views

Maximum possible memory usage: 16.2G (874% of installed RAM)

Why the MySQL is saying that can use about 16GB of RAM even if my server has only about 2GB? # ./mysqltuner.pl >> MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net> >> Bug ...
3
votes
2answers
112 views

Searching for pairs of date/time values in a single row

I have a legacy IBM DB2 database table that contains lots of records of phone calls. It has columns for ID, customer number, employee who took the call, etc. The date/time for each call is kept in a ...
3
votes
2answers
63 views

Fill in missing dates with data value from previous populated date for group

Picture help desk tickets that gets transfered between departments. We want to know what the department is at the end of the day for each ticket for each day that the ticket is open. The table ...
3
votes
1answer
133 views

How to tune the query on all_constraints in Oracle 12c?

How to tune the following query, as it's taking around 8 seconds: select constraint_name,table_name from all_constraints where r_constraint_name in ...
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,...
3
votes
2answers
915 views

Optimize a query on two big tables

I have a very important query in my system that is taking too long to execute due to huge amount of data on tables. I'm a junior DBA and i need the best optimization I can get for this. Tables have ...
3
votes
4answers
2k views

Optimising MySQL

As per this post, it looks like I'm having some serious issues with MySQL optimisation. Unfortunately, I have no idea where to start! I'm running a DirectAdmin-based CentOS dedicated server. It's an ...
3
votes
1answer
3k views

How to improve MySQL Server Performance..??

As a MySQL DBA most of the times we are supposed to optimize a poorly performing MySQL Servers. Now my question is where to start from like we will need to find out many things as 1.Find the ...
3
votes
3answers
151 views

Optimisation Newbie: How much of a 'sin' is redundancy?

I've been developing for a while, but never really had to deal with DB/scaling issues before. That's suddenly changed and I've found myself in the deep end. I have 2 SQL tables, as such: VOTES ...
3
votes
2answers
2k views

How can I optimize performance on a MySQL ndbcluster?

So, this is what I have: A simple table classes__to_students: |class |student| --------------- Math Alice Math Bob Math Peter Math Anne Music Bob Music Chis Music ...
3
votes
2answers
208 views

IN Clause - performance difference between listing values and query

I changed this query SELECT ... FROM linkedServer.DB.Schema.Table1 t1 LEFT JOIN linkedServer.DB.Schema.Table2 t2 ON t1.ORDER_ID = t2.ORDER_ID WHERE t1.BRANCH_ID NOT IN ( '009991', '009992', '...
3
votes
2answers
793 views

cross database way to force join strategies

[how to enforce the query to use certain join algorithm] Knowing that there are several joining algorithms [Nested Loop; Sort Merge; Hash Join], when implementing a query , is there any technique to ...
3
votes
1answer
453 views

How un-clustered is a CLUSTER USING table in PostgreSQL

I have some tables which benefit greatly from CLUSTER ON/CLUSTER USING in Postgres SQL: # CLUSTER table USING index_name; # ANALYZE VERBOSE table; # CLUSTER VERBOSE; A maintenance task periodically ...
3
votes
1answer
3k views

Correct use of VOLATILE COST (and ROWS) indications in Postgresql stored procedure

While looking at several examples of pl/python and pl/pgsql, I have seen many - but not all - using volatile cost. ie: CREATE OR REPLACE FUNCTION my_function() RETURNS setof record AS $BODY$ -- ...
3
votes
2answers
4k views

How to optimize this dynamic stored procedure

I have a stored procedure which is retrieving data using 20 tables. Sample of the procedure: CREATE PROCEDURE GetEnquiries ( @EnquiryDate DATETIME = NULL ) AS ...
3
votes
2answers
407 views

What are the key points to avoid table locks?

MySQL is using at my company and most of the database administration works on trying to solve the table locks. What are the key points to avoid table locks(I mean at database side and even at software ...
3
votes
1answer
59 views

Increase in IO Wait after adding indexes with postgres

I've recently been put in charge of a system that has been fairly untended for a period of time. The majority of activity is via queued worker jobs based off of data feeds from other systems/data ...
3
votes
2answers
44 views

Poor SELECT performance on recheck cond with data and large (millions of rows) partial index

Query: EXPLAIN (ANALYZE, BUFFERS) SELECT COUNT (*) AS "count" FROM "Posts" AS "Post" WHERE "Post"."createdAt" > '2015-08-19 14:55:50.398' AND "Post"."new" = TRUE; Index: CREATE ...
3
votes
3answers
165 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 ...
3
votes
2answers
1k views

How to speed up select distinct?

I have a simple select distinct on some time series data: SELECT DISTINCT user_id FROM events WHERE project_id = 6 AND time > '2015-01-11 8:00:00' AND time < '2015-02-10 8:00:00'; And it ...
3
votes
1answer
1k views

Find out beforehand how many records a query has

I develop reports in ExtJS resulting of queries in SQL Server 2012. I use Ext.Grid with paging, therefore I can use offset to limit the amount of records a query will return. But for this ExtJS ...
3
votes
3answers
696 views

This query is choking the server. How can I improve it?

Following query is hogging a lot of resources, as much as that the queries are getting locked down & CPU usage goes through the roof! The MySQL Processes list shows that as the number of requests ...
3
votes
2answers
191 views

What are the best Database Analytics Companies for analyzing several 1000s or Mio. of products,sum them together and analyze them for statistics?

We will get several thousands products in our database. The problem is that one product can have several different names e.g. "iPad 16GByte UMTS schwarz" or "iPad 16GB 3G black" and so on. But they ...
3
votes
1answer
56 views

Indices for a join query with datetime range

Given the following query: SELECT DISTINCT COUNT(DISTINCT "patients"."id") FROM "patients" INNER JOIN "transactions" ON "transactions"."patient_data_id" = "patients"."patient_data_id" WHERE "...
3
votes
1answer
932 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 ...
3
votes
1answer
110 views

Simulate more RAM with SQL Server

Background: I'm in the process of optimizing a Data Warehouse style query with big dimension tables. The optimizer does not like to pick the typical star schema join plan at the moment. When I force ...
3
votes
2answers
163 views

Do MySQL optimizations hold for MariaDB also?

I am a developer who will be starting a new project soon in which database performance will be crucial. The current candidate for the backend is MariaDB. I have worked with MySQL and MariaDB before in ...
3
votes
1answer
286 views

PL/pgSQL functions and optimizations fences?

I am newbie on DB stuff. And my DB knowledge is very old (around MSSQL2000). All what I remember is, the stored procedures are faster than ordinary queries because it avoid query compilation and ...
3
votes
1answer
438 views

What's the optimal server configuration for fastest inserting in a MySql database in this scenario?

I have a MyISAM table partitioned by range with 90 partitions representing the last 90 days. I expect this table to receive somewhere between 500 million to 1 billion records before i start rotating ...
3
votes
1answer
234 views

Query optimization with extreme statistic

I have a table: mysql> describe table_1; +---------------+------------------+------+-----+---------------------+----------------+ | Field | Type | Null | Key | Default ...
3
votes
3answers
180 views

How to optimize a log process in MySQL?

In my project, I have about 100.000 users and can't control their behavior. Now, what I would like to do is log their activity in a certain task. Every activity, is one record which includes columns ...
3
votes
2answers
4k views

How to setup mysql on tmpfs?

How to setup mysql on tmpfs ? I am reading a tutorial in french, where the author do it this way : 1.) Create a tmpfs directory : mkdir /var/tmpfs mount -t tmpfs -o size=1G tmpfs /var/tmpfs chown -...
3
votes
1answer
271 views

How does the MySQL Query Optimizer react to a SELECT COUNT sentence?

Let's say we have a table called "customers" with these columns ("id", "name", "country", "created_at). There's an index by ("country", "created_at"). If SELECT COUNT(*) FROM customers WHERE ID &...
3
votes
3answers
2k views

How to restructure this slow query containing subquery?

This query takes about 2-6 seconds to run depending which tag id(s) is/are selected: SELECT t2.id, t2.name, count(*) as kpl FROM (SELECT pt1.post_id FROM posts_tags pt1 WHERE pt1.tag_id ...
3
votes
3answers
67 views

IN Clause - listing values VS query

My app runs a lot of queries like this SELECT ... FROM Table WHERE CategoryID IN (SELECT CategoryID FROM OtherTable WHERE ..) To save performance I decided to cache the second subquery in the ...