In the context of a database, optimisation refers to the process of the query optimiser selecting the best query plan to execute.

learn more… | top users | synonyms

2
votes
2answers
402 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 ...
2
votes
1answer
44 views

Convert Log table days in a state

This is simplified but represents the problem I am trying to solve. We have a table containing 5-10 million rows in a format similar to the following... Input Table Date Item Moved to Box Oct-1 ...
2
votes
2answers
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 ...
2
votes
1answer
136 views

Optimize Percona MySQL InnoDB 3.2TB database

(Before you will call me an idiot or start laughing, please remember it's a real deal on my side and I cannot cancel this assignment as it is a part of a bigger project) I have a database server that ...
2
votes
1answer
132 views

Is there a negative effect if I place an index on every column in a wide table?

We have a wide dataset that isn't very tall (say 250K rows, 90 columns). We will be providing users with an interface that allows them to generate arbitrary queries against this dataset. They can ...
2
votes
1answer
149 views

Clustered composite key high fragmentation and fill factor

I've recently made a table to hold the language preferences of my users as follow: CREATE TABLE [dbo].[systemUserLangPreference]( [systemUserID] [int] NOT NULL, [langID] [int] NOT NULL, ...
2
votes
2answers
1k views

Optimized query to fetch a record from 10 million entries

A table has 2 fields | UniqueKey | TimeStamp | ------------------------- | xfsddddq | 1024125412 | | xfstttdx | 1024125413 |< | xfsdxxau | 1024125415 | Estimated, there are 10 million ...
2
votes
1answer
147 views

Has postgresql a fast add column optimization?

When adding not-null columns with a default value - does Postgresql optimizes this operation? In case the table has n rows, a not-optimized alter-table-add-column would yield n writes of the default ...
2
votes
1answer
51 views

Is there any way to get the optimization time in MySQL?

I'd like to see the time optimizer takes to optimize a query. Is there any way to get that time?
2
votes
1answer
1k 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$ -- ...
2
votes
1answer
126 views

How to know what to investigate and how for a VERY slow stored procedure

I have inherited a large and slow stored procedure and it's giving me a nightmare: I'm not a DBA, although I have some knowledge, but I don't know where to start to identify this bottleneck. I have ...
2
votes
1answer
2k views

MySQL / InnoDB: Does daily optimization improve performance?

Would performing daily optimizations of tables increase performance for MySQL and InnoDB? I have never did this before, but I have seen this module for Drupal http://drupal.org/project/db_maintenance, ...
2
votes
5answers
506 views

Help optimizing query in the MySQL slow-query log

Our database is set up so that we have a credentials table that hold multiple different types of credentials (logins and the like). There's also a credential_pairs table that associates some of these ...
2
votes
2answers
200 views

In MySQL, should I add an index even if the query that scans the table is only ran once a month?

Concretely, is it worth it to add an index to a query that scans the table of a mysql database, even if that index would only be used once a month? For example, having a users a table and then ...
2
votes
2answers
95 views

Is it always faster to create indexes after loading data?

I have a large number of records (~1 billion) that I need to load into MongoDB (actually TokuMX, but whatever). I have about 6 different indices I need to create on the collection. Is it always faster ...
2
votes
1answer
224 views

Specifying columns in LEFT JOIN through subquery beneficial performance wise?

I'm trying to understand how JOINS are handled, and was wondering about the following: Imagine a big query concerning a lot of tables, I'm really looking for small optimizations as it's a pretty ...
2
votes
1answer
691 views

How to optimize a query that includes counts of rows in multiple tables?

My query looks like this: EXPLAIN SELECT ... FROM USER u LEFT OUTER JOIN (SELECT COUNT(*) notified_count, user_id FROM user_email_tracking ON u.id = email_tracking WHERE email_type_id = 4 ...
2
votes
2answers
121 views

Optimize query or model, to get an population overview in time

I think I've got a very simple model and query, but I cannot find a way to make use of indexes to boost performance. I wonder if there is a trick in my model, or MySQL query, to speed up the ...
2
votes
1answer
918 views

How to speed up mysql query?

I have query : SELECT `school`.`ARCO_name`, `student-`.`ClassSize_7`, `student-`.`ClassSize_8`, `degree_o`.`degree_code`, `accredit`.`full_faculty_3`, ...
2
votes
2answers
916 views

In DB2, how can I disable NLJOIN?

I want to indicate to the optimizer, via SET or optimization profiles, that NLJOIN shouldn't be used at all when optimizing a query. I've been looking for this for a while without any success. I ...
2
votes
1answer
290 views

How to get rid of clustered index scan?

I have a query that was generated by C#/Linq-To-Entities code. As with this kind of query, it looks seriously ugly and I doubt it would help to include it here. But I have run it through a query ...
2
votes
1answer
40 views

General purpose view for last value in a group

I am trying to create a view to show the last value in the group. Looking for a more general purpose query. CREATE TABLE a ( id INTEGER, seq INTEGER, val VARCHAR2(16), PRIMARY ...
2
votes
2answers
117 views

Convert to Self JOIN

I have a correlated sub query, which is taking 16 Minutes to execute. How can I convert it to join or optimize it. SELECT SSF.SM_StockCode, SSF.ST_ItemSize FROM Stock AS SSF WHERE ...
2
votes
2answers
507 views

Setting innodb_buffer_pool_size in for Mysql

We have a dedicated DB box with with 8 GB of RAM and running Redhat linux 64 bit OS. Our MySQL Data + Index size almost 7.8 GB. About setting innodb_buffer_pool_size, I have read 2 suggestions Set ...
2
votes
1answer
99 views

Why isn't the rows of an EXPLAIN accurate?

I have the following table: mysql> select count(*) from employees; +----------+ | count(*) | +----------+ | 10000 | +----------+ 1 row in set (0.03 sec) I do an EXPLAIN: ...
2
votes
2answers
109 views

Optimizing index creation

One thing that has bothered me for awhile is creating many many indexes for the one table where the query only differs slightly or contains many of the same columns. An example case would be: table1 ...
2
votes
1answer
2k views

MySQL Explain: Using index, using temporary, using filesort. Can this query be improved?

I'm working on a event tracking system which uses a handful of lookup tables as well as the primary logging table. In a report I'm writing, an object can be selected to view statistics against. The ...
2
votes
1answer
891 views

Is a query with UNIX_TIMESTAMP using indexes in mysql?

If I use .. WHERE UNIX_TIMESTAMP(`some_timestamp`)>NOW() in a SELECT, does this query still make use on an index, set on some_timestamp ?
2
votes
1answer
98 views

Optimising query on view that merges similar tables with a clear discriminator

Using PostgreSQL 8.4, I have a number of tables that have a very similar structure, but that belong to different categories: CREATE TABLE table_a ( id SERIAL PRIMARY KEY, event_time TIMESTAMP ...
2
votes
1answer
2k views

PostgreSQL tree structure and recursive CTE optimization

I'm trying to represent a tree structure in PostgreSQL (8.4) to be able to query the path from the root to a given node or to find all the nodes within a sub-branch. Here is a test table: CREATE ...
2
votes
1answer
246 views

Changing to InnoDB increases iostat

I have changed my MyISAM tables to InnoDB and from that day, iostat is increasing continuously. My findings on the server say that mysqld is the one who is increasing iostat. But how can it increase ...
2
votes
1answer
695 views

Poor MySQL for Drupal on Shared Hosting

My Drupal site is on a shared host where I cannot modify the my.cnf configurations but the database seems to be performing poorly. Here are some of the flagged issues that I can see... In ...
2
votes
2answers
128 views

Why isn't the following query using the `always` index?

This morning I started receiving load warnings on one of the MySQL servers I manage. SHOW FULL PROCESSLIST revealed that the culprit was the following (poorly written) query: SELECT * FROM movies, ...
2
votes
1answer
304 views

In DB2, what are the alternative ways of evaluating an index's (or several indexes) benefit?

I'm trying to evaluate different approaches to analyzing the benefit of changing the set of indexes that are available. If I pick some representative queries from my workload, I can run EXPLAIN and ...
2
votes
2answers
280 views

Inserting to table: Insert rows with Left Outer Join or Insert rows then Update?

I have a table of Orders and another table of Deliveries. The Orders table will contain a reference to the Deliveries table if there is a corresponding row. Not every row in Orders will have a row in ...
2
votes
1answer
139 views

Optimizing query on a partitioned table

Tried to find an answer for this, but couldn't. So bear with me and apologies if this has been answered elsewhere. It may be just me not being able to find it. Here's the setup: SQL 2008 R2 EE. Table ...
2
votes
2answers
2k views

Optimizing mysql queries running on 50 million rows

Is there any technique/advice in order to optimize the performance of the queries below? According to my needs, my average db size will be approximately 30mill. rows per day, so every second less, ...
2
votes
2answers
319 views

Schema design for privacy settings in MongoDB

I have a set of documents that can have various privacy settings around them: They can be completed public (any registered user) can view them They can be viewable only by people following you (this ...
2
votes
2answers
3k views

How do I force a JOIN to use a specific index in MySQL?

I have a query that JOINs 2 tables; lineitem and part, select sum(l_extendedprice* (1 - l_discount)) as revenue from lineitem force index for join (l_pk), part where ( ...
2
votes
1answer
740 views

Oracle 10g using collection with sql

I have a stored procedure that takes collection of objects. (TABLE OF MyCustomType). Inside the procedure I'm trying to join this parameter with real tables. For example, something like create or ...
2
votes
1answer
1k views

LEFT JOIN / WHERE clause with is null and false Estimated Number Of Rows

I have a table named Product and a table named ProductPropertiesCountry. Product is the definition of a product (description, price, productId, etc) ProductPropertiesCountry contains restrictions on ...
2
votes
1answer
2k views

Optimizing MySQL for Read-Only?

We have 14 GB worth of CSV's which total 138 million rows. I imported this into a MySQL table first with InnoDB, and then tried again with MyISAM. In both cases, a simple SELECT on primary key ...
2
votes
1answer
593 views

Inserting into three different tables, using foreign keys — 500000x each

We're working on refactoring many of our offline (python) scripts. I'm very new to any kind of SQL, but have been trying to learn as much as I can, especially in the performance field. We have a ...
2
votes
1answer
1k views

Why does MySQL only sometimes use my index for a range query?

I have a table of IP address ranges and their country, here is the structure: CREATE TABLE `geoIP` ( `startBlock` int(11) unsigned NOT NULL DEFAULT '0', `endBlock` int(11) unsigned NOT NULL ...
2
votes
1answer
282 views

datetime index skipped for same query different params

I am running two queries with same query but different parameter and one of them is extemely slow while the other is fast. Both the results are alomost the same. (500 and 575) Any idea why it skips ...
2
votes
2answers
105 views

Performing a “Spread Negitive” over a dataset

I have a situation where a table that represents outstanding balances and credits are both contained in one table. What I need to do is apply all outstanding credits (preferably in order of oldest ...
2
votes
2answers
164 views

Count() versus keeping a column with a total count [duplicate]

Possible Duplicate: Storing vs calculating aggregate values Say I have a table full of photos that each have an albumId that is a foreign key to an album table. To get the amount of photos ...
2
votes
1answer
42 views

How to determine cost/benefit of adding an index?

According to Craig Ringer: While it's usually a good idea to create an index on (or including) your referencing-side foreign key columns, it isn't required. Each index you add slows DML operations ...
2
votes
1answer
54 views

What is the best storing device for DB transaction log?

I'm using MySQL with InnoDB storing engine. Transactional log is placed to dedicated HDD. Still as it is HDD atopsar shows big load (>50%) to this HDD because of big amount of small IOs (about 50 ...
2
votes
0answers
42 views

Efficiently joining two irregular series representing changes

(This is a PostgreSQL-specific question, using version 9.1, but could be upgraded to 9.3.) I have a number of tables that represent a series of changes. These tables all have the same structure: ...