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

0
votes
1answer
129 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
80 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
3k 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
2answers
132 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 ...
6
votes
3answers
5k 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 ...
1
vote
2answers
287 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
66 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
550 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 ...
0
votes
1answer
369 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 ...
-1
votes
1answer
88 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 ...
1
vote
1answer
855 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 ...
0
votes
1answer
95 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. ...
2
votes
1answer
1k views

Slow Queries Not Logging

I am attempting to enable slow query logging on our server in order to identify any queries that could use optimization. Sounds simple enough, however my file is not being written to. I get no errors ...
4
votes
2answers
963 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
135 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 ...
1
vote
1answer
68 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 ...
0
votes
1answer
63 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
107 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
705 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
410 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
153 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
1k 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 ...
4
votes
1answer
215 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 ...
4
votes
3answers
299 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
723 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
205 views

Query optimization with extreme statistic

I have a table: mysql> describe table_1; +---------------+------------------+------+-----+---------------------+----------------+ | Field | Type | Null | Key | Default ...
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 ...
2
votes
0answers
355 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
1answer
3k 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
117 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
2k 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
249 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
394 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
849 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
664 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
106 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
196 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
2k 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
178 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
166 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
203 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
487 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
1k 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
44 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
2answers
155 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
34 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
310 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 ...