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

1
vote
1answer
125 views

Proper indexes or anything else to optimize

I need help to optimize the query to avoid using "Using temporary", "Using filesort". CREATE TABLE `target_bahrain_pepsi` ( `id` int(11) DEFAULT NULL, `col_value` varchar(50) DEFAULT ...
3
votes
1answer
4k views

Subqueries run very fast individually, but when joined are very slow

ypercube solved the problem. Subqueries were totally unnecessary, and the whole thing works with plain joins. It is still strange that MySQL's optimizer could not make use of my original query, ...
2
votes
2answers
372 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 ...
0
votes
1answer
568 views

Optimising MySQL query with lots of self-joins

I have an entity_relationship table that describes relationships between entities. Each relationship will have 2+ entities involved. In some cases it can be said that one entity is a constituent of a ...
5
votes
2answers
1k views

Bulk Data Loading and Transaction Log

im currently working on a project which bulk import data from flat files(csv) about 18 differents files each linking to a specific table through some store procedure. I followed the steps as advised ...
3
votes
1answer
764 views

Mysql Server optimization tips needed! (InnoDB)

I've recently been trying to optimize our mysql server but I've encountered some pretty serious problems on the way. We're running a game server which is coded in C#. We have two dedicated servers. ...
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 ...
1
vote
2answers
230 views

XML Large DataBase is the best? [closed]

Is the best way to implement a large database is XML? Or is there a better way? 1 - reduce the time complexity 2 - Reduce the amount of traffic between the tables And other factors ... Which ...
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 ( ...
5
votes
1answer
237 views

Determining which isolation level is appropriate

This is a homework question. For the following transactions state the isolation level that will maximize throughput without lowering the integrity of the database. Explain the answer. ...
1
vote
1answer
186 views

Is there a way to write intelligent queries against a merge table in MySQL that represents a bunch of tables partitioned by date?

I have a set of MySQL tables that are partitioned by date, with a merge table representing all of them. For the query I am writing, the results are joined by date, so rows in table A_2012-12-05 will ...
5
votes
1answer
230 views

Walking a BTREE index as far as possible in MySQL

Suppose one has a column of words on which one builds a BTREE index: CREATE TABLE myTable ( words VARCHAR(25), INDEX USING BTREE (words) ); LOAD DATA LOCAL INFILE '/usr/share/dict/words' INTO ...
0
votes
2answers
646 views

Slow query with straight_join for small result

I have a performance-problem with a query in my application and I don’t understand the behavior of mysql. The query consists of different joins, especially the join to the mentioncache-table. If the ...
4
votes
1answer
1k views

How much does table size affect performance?

Lets say I have a MyISAM table (amongst other tables) with 2938347 rows. This table is merely being used for logging and doesn't get emptied very often. By not regularly archiving or deleting these ...
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 ...
0
votes
0answers
47 views

mysql select query optimization [duplicate]

Possible Duplicate: mysql select query optimization I have two tables, testa and testb. CREATE TABLE `testa` ( `id` INT(10) NOT NULL AUTO_INCREMENT, `name` VARCHAR(50) DEFAULT NULL, ...
3
votes
3answers
178 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
0answers
35 views

Doubt :: my sql performance tuning [duplicate]

Possible Duplicate: MySQL performance tuning I would like to optimize my MySQL DB and I identified some important points as follows: enabling query cache, using memory storage (I know ...
1
vote
0answers
378 views

Finding the bottleneck by analyzing Munin graphs

We’ve been experiencing some issues with our database server during peak hours. In our Nginx error-log we can see that a lot of requests get timed out. The requests are also very slow. We have had ...
2
votes
1answer
825 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 ...
3
votes
2answers
105 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 ...
4
votes
1answer
200 views

250 billion rows - computing interconnectedness on a really huge scale

I have a table of approximately 700k rows, each identified by a unique itemnumber. Each row/item can be associated with any of the other rows/items in the table by calculating a single numerical ...
0
votes
0answers
69 views

Load time implications of storing data in multiple rows vs serialized data

I am currently storing data in fields as shown below. Each of these pricing fields has two database rows associated with it, and there is up to 24 of these tables for a house. This therefore means ...
1
vote
2answers
464 views

How to isolate general Oracle 10g issues through SQL

Background: I've been working on a performance analysis on a system with major performance issues on a Oracle 10g server. Much of the SQL is badly written, or at least not written with performance in ...
2
votes
1answer
992 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 ?
6
votes
2answers
721 views

Adding a Clustered index to a HEAP table on someone else's app

We're using a proprietary application based on SQL Server 2005, which has many HEAP based tables (that is, no Clustered index). Over the years, these tables have grown badly fragmented (e.g. 99% ...
0
votes
1answer
132 views

MySQL query optimization

I'm quite new to big MySQL queries (I used to extract raw data from tables then manipulate them with PHP) so I'd like to know if my query below is too "heavy" or slow for some reason (it seems to be ...
5
votes
3answers
487 views

Database Indexing - Maintenance Jobs

I have created a script that runs every night to rebuild & re-organize indexes based on the fragmentation, Indexes with Fragmentation > 30% are rebuilt, Indexes with Fragmentation 10% - 30% are ...
1
vote
1answer
571 views

PostgreSQL 9.1 - Asynchroneous commits performance boost

During peak hours our users experience a lot of timeouts due to an overloaded database server. We are currently trying to make optimizations in order to cope with the high traffic. We read in the ...
1
vote
1answer
2k views

MySQL: how to optimize the stored procedure?

MySQL 5.5.28 Running pt-query-digest on a slow query log, I get something like this: # 1.2s user time, 10ms system time, 22.30M rss, 114.48M vsz # Current date: Wed Oct 24 23:44:05 2012 # ...
7
votes
1answer
564 views

Is the optimisation fence behaviour of a CTE (WITH query) specified in the SQL:2008 standard? If so, where?

I see frequent references to WITH queries (common table expressions, or CTEs) acting as an optimisation fence, where the server isn't permitted to push filters down into the CTE queries, pull common ...
3
votes
3answers
2k views

MySQL optimization - year column grouping - using temporary table, filesort

I have a transactions table which is having 600,000 records, I need to list the count for the dashboard on financial year basis. The table used is MyISAM. I tried adding index for the transaction date ...
1
vote
1answer
678 views

Slow insert with MySQL full-text index

I use a full-text index in a MySQL table, and each insert into this table takes about 3 seconds. It seems that MySQL rebuilds (a part) of the full text index after each insert/update. Is this right? ...
1
vote
1answer
2k views

Slow complex query with group/order

I have a problem with a very important query in my application. It is very slow when adding group and order to it. So the full query is: SELECT m.id, m.title, m.title_text, ...
1
vote
2answers
905 views

Slow mysql fulltext query

I have a little problem with the performance of a mysql-query which uses a fulltext index. The following query SELECT Mention.id FROM mentions AS Mention WHERE (MATCH (`Mention`.`title_text`, ...
2
votes
1answer
101 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
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 ...
4
votes
3answers
253 views

How does the T-SQL optimizer handle “and false”

In SQL SERVER 2008 I'm thinking about a query such as: SELECT ... FROM ... WHERE ... AND @a=1 UNION SELECT ... FROM ... WHERE ... AND @a=2 Given that @a is clearly the same value, and therefore ...
1
vote
1answer
94 views

What is the best relationship model for high performance?

I want to model a relationship between Customer and Transaction. However, because Customer is only described by a unique string and no extra info, I have two possibilities to implement it in the ...
1
vote
2answers
91 views

Efficiency for contact-contact relationships schema

I'm migrating data from a schema where people and organisations are separate tables to one where people and organisations are all treated as contacts (they share a lot in common). Currently, the ...
2
votes
1answer
128 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
3k 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, ...
1
vote
1answer
2k views

Planning simple CMS: How much relation?

I'm currently in the process of designing the database for a rather minimalistic Content Management System I'm working on alone. It should allow scalability while remaining speed, especially on boxes ...
0
votes
1answer
92 views

Why searching with $all in Mongodb is FAR SLOWER than the EQUIVALENT search?

$all with only one item is equivalent with normal search right? Here is the log without $all (I used 4700 test cases with 30 thread): Thu Sep 20 17:27:05 [conn11] query newisikotagte15.tablebusiness ...
5
votes
1answer
645 views

Query performance while using sqlcmd [duplicate]

Possible Duplicate: Running queries with SQLCMD vs. Running queries with SSMS I have a number of scripts that query the data from different tables and consequently update them. I have ...
1
vote
1answer
233 views

query optimization delete nested query [closed]

Can the following query be optimized ? DELETE FROM COLOR_TAGS WHERE COLOR_PROJECTID = $id AND NoteID IN( SELECT ID FROM note_note WHERE BibID=$bid AND isdel=0);
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 ...
3
votes
1answer
132 views

Analysing A Query Plan

Why is the following query slow? select count(*) from [dbo].[mt_dispatch_link] , [dbo].[_mt_dispatch] [_mt_dispatch] where (mt_dispatch_link.contract_id_1 = _mt_dispatch.contract_id ...
7
votes
2answers
5k views

Why is this query not using my nonclustered index, and how can I make it?

As follow up to this question about increasing query performance, I'd like to know if there is a way to make my index used by default. This query runs in about 2.5 seconds: SELECT TOP 1000 * FROM ...
11
votes
4answers
3k views

Is it possible to increase query performance on a narrow table with millions of rows?

I have a query that is currently taking an average of 2500ms to complete. My table is very narrow, but there are 44 million rows. What options do I have to improve performance, or is this as good as ...