In the context of a database, optimisation refers to the process of the query optimiser selecting the best query plan to execute.
0
votes
1answer
48 views
Faster joining on 'most recent' related rows
I have a bug tracking application. The bug table stores very little,
because every state change to a bug happens by adding a comment. For
example, the current state of a bug is determined by the state ...
1
vote
1answer
88 views
Does PostgreSQL optimize queries in transaction?
In my app I need to make big imports from user files, and to achieve that all records are updated/created I do it inside transaction. But before it I need to update massive, already existing in DB, ...
0
votes
3answers
253 views
Mysql optimization help
I've tried optimizing mysql as good as I could, but apparently I'm not to good at it :-)
So I come to you guys for help.
Server specs are:
AMD Opteron CPU 8 cores
16 GB RAM
2x2.000 GB HDD 7.200 RPM ...
2
votes
0answers
161 views
MySQL: Improve performance for one row insert into table with unique constraint
I have a table with ~ 40 mil records, with a unique index on the url collumn, of type varchar(255).
Now the insert speed is about 30/s, is this expected? how could I improve it?
I can't use bulk ...
1
vote
4answers
111 views
Normalization of DB
I have designed a database using MySQL likewise
My question is what else i can do to normalize the DB, for now i am using InnoDB engine for the table to maintain the relation among different tables.
...
4
votes
1answer
262 views
Indexing strategy when using the between operator SQL Server 2008
I have a large table ~25 million rows with the structure
CREATE TABLE [dbo].[rx](
[pat_id] [int] NOT NULL,
[fill_Date] [date] NOT NULL,
[script_End_Date] AS ...
0
votes
2answers
61 views
Academic Journals and Reviews for Database Systems [closed]
I want to start reading up on the latest advances in database technology. Can anyone tell me what are the leading academic journals on the subject?
Also, I have a friend doing a PhD in another area ...
4
votes
1answer
90 views
Does the order of fields in SELECT query matter when using composite indexing?
I understand that the order of columns in the index itself matters immensely; however, what about the order of columns in the subsequent SELECT queries that make use of that index?
For example, if I ...
1
vote
3answers
191 views
Good, Bad or Indifferent: WHERE 1=1
Given this question on reddit, I cleaned up the query to point out where the issue was in the query. I use comma first and WHERE 1=1 to make modifying queries easier, so my queries generally end up ...
0
votes
0answers
34 views
optimize this query [duplicate]
Possible Duplicate:
Slow query performance
My database contains 20,000 rows and the following query is slow. Please help me to speed this up.
SELECT r.report_id, ...
2
votes
1answer
233 views
Mysql - show results from 3 tables excluding NULL or EMPTY values for certain fields
I have 3 simple tables with polls, users and votes. I'd like to show all polls ordered by clean count of votes (sometimes either poll_id or user_id don't get inserted).
Furthermore I'd like to show ...
3
votes
1answer
154 views
Query optimization with extreme statistic
I have a table:
mysql> describe table_1;
+---------------+------------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default ...
4
votes
1answer
97 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 ...
2
votes
0answers
130 views
Best book on query optimization, rewriting, refactoring [closed]
I have a specific problem: huge amounts of Web-related data in Postgres db. The queries running against the data are often very inefficient.
But in a more general sense, I need a book that explains ...
0
votes
0answers
78 views
What is faster - multiple access of table or using temporary table?
I have created two tables
create table one(ref_no int, name varchar(100), design varchar(100));
create table two(SNo int auto_increment primary key, use_date Date, ref_no int references one(ref_no), ...
0
votes
1answer
994 views
Mysql 5.5: Optimizing all tables with mysqlcheck - is it useful to run the -check option along with -optimize?
I plan on running 'mysqlcheck --optimize --all-databases' to clear up some fragmentation.
Would it be useful to run -check with this option or does the -optimize already take care of that?
I am only ...
1
vote
1answer
96 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
412 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
124 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
129 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 ...
4
votes
2answers
246 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
364 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
92 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
143 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 ...
1
vote
2answers
177 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
99 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
104 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
140 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
216 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
263 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
708 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
39 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,
...
0
votes
0answers
119 views
Which update is faster using join or sequential?
My this question is in sequence of my previous question I asked.
I could write two solutions using Stored Procedure instead of trigger or nested-query .
Both use a helper function ...
2
votes
2answers
82 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
31 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
164 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
211 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
90 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
130 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
49 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
280 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 ...
1
vote
1answer
166 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
295 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
104 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
167 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
118 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
749 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
# ...
4
votes
1answer
164 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 ...
2
votes
3answers
499 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
2answers
266 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?
...
