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

1
vote
2answers
133 views

Prune unused joins

I have expected that selecting from a view would not join the tables from which I don't query any values, but it doesn't seem to be the case. I have a bunch of tables with the following structure: ...
0
votes
3answers
392 views

Is it worth to separate columns into multiple tables for one-to-one relational table

I need to make a decision for database structure on whether to separate one-to-one relational columns into multiple tables and link with one relationship id or just add all columns into one table. ...
7
votes
3answers
1k views

Splitting SQL query with many joins into smaller ones helps?

We need to do some reporting every night on our SQL Server 2008 R2. Calculating the reports takes several hours. In order to shorten the time we precalculate a table. This table is created based on ...
0
votes
2answers
477 views

Use timestamp(or datetime) as part of primary key (or part of clustered index)

I use following query frequently: SELECT * FROM table WHERE Timestamp > [SomeTime] AND Timestamp < [SomeOtherTime] and publish = 1 and type = 2 order by Timestamp I would like to optimize ...
0
votes
0answers
363 views

Using something better than UNION for combining multiple queries

I have one big query which is based on 12 smaller queries that I have put together using UNION. A relative simple example of my query: (SELECT 'type1' AS 'type', fieldOne AS someField, ...
2
votes
1answer
49 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?
1
vote
3answers
238 views

Table clean up taking too long

I am selecting around 10 million rows which are invalid from table grid into ##grid_temp and deleting their related info from grid_info table. I am running this in a python script. It is taking more ...
1
vote
1answer
431 views

MySQL subqueries that use range based on values of main queries don't use indices properly

I think I've isolated a problem that has been affecting many of my queries lately. And would like some help to figure out a solution for this. Ok so my findings are that a normal query that runs very ...
-1
votes
1answer
86 views

Optimization of a select statement

I'm using MySQL and have a table user_data like this: user_id int(10) unsigned reg_date int(10) unsigned carrier char(1) The reg_data is the unix timestamp of the ...
0
votes
1answer
28 views

Separate table by often changed / not often changed data?

I have a application which querys different gameservers, and stores for example if the server is online, and how many players are connected. But of course there is also data which is changed rarely. ...
0
votes
2answers
230 views

Is there a good reason to store big text data outside database?

I was analyzing my MySQL database performance and found that tables containing text fields are just huge, larger than 1Gb. This data is only used when loading single record (like article or blog ...
7
votes
2answers
182 views

Is there any way to prevent the memo structure from being pruned?

We know that the memo structure is pruned and some expensive alternative plans are discarded during optimization. I was wondering if there is any way to prevent this and let the optimizer just ...
1
vote
1answer
74 views

Query optimization when no row is returned

I am using explain to figure out what is happening in my query which is: explain select t from c where u1_id = 1 group by t; I see "Using where" in the Extra column of the resultset. But this only ...
9
votes
5answers
896 views

Force sql server to run query conditions as written?

Im using Sql server 2008 R2 And I have this pseudo query (SP) : select ... from ... WHERE @LinkMode IS NULL AND (myColumn IN (...very long time exeted query...)) ... ... The ...
0
votes
1answer
316 views

optimize big sql query

My query has to return a statistics for example for march containing productname and price and its sidedishes (1:n relation) with the right price of each sidedish and the right tax for each sidedish ...
0
votes
1answer
406 views

Large INSERTs performance optimization

I have 15 Amazon AWS EC2 t1.micro instances which simultaneously populate Amazon RDS MySQL d2.m2.xlarge database with data using large INSERTs (40000 rows in query). The queries are send ...
50
votes
6answers
15k views

How to determine if an Index is required or necessary

I've been running an auto-index tool on our MS SQL database (I modified a script originating from Microsoft that looks at the index statistics tables - Automated Auto Indexing). From the stats, I now ...
2
votes
2answers
182 views

Why does adding LIMIT to my query make it crawl?

Simple query: select sum(score) total,name,gender,dob,country from users join scores on users.id = scores.user_id where date between '2012-01-01' and '2012-01-31 23:59:59' group by scores.user_id ...
0
votes
0answers
105 views

InnoDB tuning with 1G of ram limit

I am trying to calculate variable moving averages crossover with variable dates. That is: I want to prompt the user for 3 values and 1 option. The input is through a web front end so I can build/edit ...
3
votes
1answer
402 views

Postgres: count(*) vs count(id)

I saw in the documentation the difference between count(*) and count(pk). I had been using count(pk) (where pk is a SERIAL PRIMARY KEY) not knowing about the existence of count(*). My question is ...
0
votes
0answers
31 views

Table partionning problem

I need your help for a partionning problem. We are using a "log_event" table which log a lot of events sent by our players (subscription, connection, play, share, etc...). We insert 5,000 row / s in ...
1
vote
1answer
320 views

Will OPTIMIZE TABLE have any impact on my data?

I'm having issues with a database that is getting quite slow. The analyzer in phpMyAdmin recommends that I run OPTIMIZE TABLE on my tables. But before doing so, I would (of course) like to know if ...
3
votes
1answer
285 views

Simple MySQL query randomly takes forever

I'm having an issue on my server with a simple PHP send emails script that runs every minute via a cronjob. The script contains a MySQL query that runs very fast most of the time, but randomly will ...
0
votes
1answer
43 views

Misunderstanding statement in MySQL documentation

I can't understand this statement in Optimizing Data Size: Declare columns with identical information in different tables with identical data types, to speed up joins based on the corresponding ...
1
vote
0answers
445 views

How can I join one to one to (many to one) without using group_concat

I have a table structure as follows: -- -- Table structure for table `emails` -- CREATE TABLE IF NOT EXISTS `emails` ( `ID` int(5) NOT NULL AUTO_INCREMENT, `email` varchar(255) COLLATE ...
0
votes
1answer
113 views

Need ideas about OPTIMIZE TABLE

Looking at a database with 10 tables and fairly active at by-the-hour changes. On the first of each month, I purge some rows from 3 tables to remove outdated material and keep the size down. All of ...
4
votes
3answers
3k views

Optimizing queries on a range of timestamps (two columns)

I use postgresql-9.1 with ubuntu 12.04. I need to select records inside a range of time: my table time_limits has two timestamp fields and one property integer. Indeed there are other info columns ...
0
votes
1answer
247 views

Optimizing ORDER BY for simple MySQL query

I'm trying to optimize this really simple query, and it gives me grief for a day now :( Seems pretty straightforward, I need to select with a JOIN from 2 tables, and get top X results sorted in a ...
0
votes
1answer
105 views

Memcache implementation

I have a Wordpress site that uses WP Super Cache plugin in order to make my blog posts and pages stay as HTML so that less PHP/MySQL code is executed. However, there's a featured box that can't get ...
1
vote
1answer
111 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 ...
0
votes
2answers
126 views

Why does using a variable make a difference in performance?

I have a table that constantly doing insert, and I am trying to run a delete script to remove old record. The delete script looks like this: DECLARE @StarDate DATETIME = '2013-01-13'; DECLARE ...
1
vote
1answer
76 views

extra steps after changing storage engine and adding index

Someone told me to look into his website for quick optimization; I'm a programmer and i don't have much experience optimizing databases. I have a php/MySQL site uses the MyISAM storage engine. It ...
0
votes
2answers
2k views

MySQL tuning (my.cnf) for very large tables and ad hoc queries [duplicate]

We are an email marketing company and recently switched our setup to MySQL. We need to configure mysql (my.cnf) for extreme performance. We have tried to configure my.cnf but heavy queries can get ...
0
votes
1answer
83 views

What index to add in case of join by two optional fields

I have query similar to the one below, it joins two tables by field which can have NULL value. The result matchs the data if both tables have the same data or consider the condition as optional. ...
0
votes
2answers
225 views

Exploring and optimizing SQL performance of your system (PostgreSQL and Hibernate)?

Imagine you have inherited some project with extensive usage of SQL queries. Let us assume that Hibernate is used as ORM and PostgreSQL as database, but thats not the point. A question is: could ...
1
vote
1answer
61 views

Mysql performance for a nullable unique id column in a large table that references a record in another table

We have a large table of contacts (8 million records) that we promote to with mailings. When these people register we store them in a different members table. But we want to go back and update the ...
1
vote
1answer
644 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$ -- ...
1
vote
2answers
84 views

Database and query optimizacion

I have a database containing three tables: tbl_database (main table is the primary), tbl_cmdatabase and tbl_blacklist. The three tables share the same structure but differ in the number of records ...
1
vote
1answer
61 views

Message system - delete message, or flag for deletion?

I'm wondering, what is the best approach about message deletion in a mysql based message system. Plan A: When the sender sends a message, it will be saved in 2 versions. One for the sender and other ...
1
vote
1answer
2k views

16 Cores 12 GB RAM server MySql Configuration - my.cnf

The server takes 20+ seconds (wait time/slow IO response time) to response to a HTTP request even with memcached and APC installed. I believe this has something to do with MYSQL since the site as lots ...
1
vote
1answer
154 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 ...
4
votes
2answers
756 views

How to Correctly Estimate the Average Number of I/O Operations my RAID 10 disk can offer?

The average Input/Output Operations per Second a disk can sustain is a very important factor in the tuning of MySQL / InnoDB databases, as it drives the speed with which Dirty InnoDB Buffer Pool pages ...
1
vote
1answer
127 views

Is there a tuning parameter for MySQL that allows you to set an on-disk gap between non-sequential primary keys?

I understand that one of the primary issues with using a non-sequential primary key is that every disk/memory write that does not come after the last known key causes a re-write of everything from the ...
0
votes
1answer
58 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 ...
4
votes
1answer
106 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 ...
0
votes
3answers
610 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 ...
1
vote
1answer
105 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, ...
3
votes
1answer
135 views

How to search for an ID and its descendants in this query?

I have the following tables: Project Unit ------------- --------------- ID | IDUnit ID | IdParent I have to select all projects that have the IDUnit ...
2
votes
0answers
381 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 ...
0
votes
1answer
277 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 ...