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
24 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
91 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
22 views
How un-clustered is a CLUSTER USING table
I have some tables which benefit greatly from CLUSTER ON/CLUSTER USING in Postgres. Data accessed at the same time is "defragmented" into a small number of disk blocks:
# CLUSTER table USING ...
0
votes
1answer
47 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 ...
0
votes
0answers
31 views
Are there any disadvantages to partitioning on financial year?
Our current set up has one table per financial year (May 1- April 30). Each table has approx 1.5 million rows. We have about 8 years of data, and will obviously be adding each year.
The majority of ...
0
votes
0answers
34 views
Optimising table efficiency format integers only [closed]
I currently have a table with 1,136,053 rows and 5 columns in a MySQL table.
They are all integer values and appear to be slowing down as I am increasing the rows.
I have indexed each of the ...
0
votes
0answers
42 views
Hierarchical data best practices
I'm looking for a best practicies\databases to solve our problem:
Currently we are using SQL Server 2008 R2 with HierarchyId. It works ok, but we have a lot of performance penalties due to this ...
2
votes
2answers
97 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 ...
1
vote
1answer
57 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
296 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
0answers
64 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 ...
2
votes
1answer
88 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
18 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
36 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 ...
2
votes
1answer
34 views
Optimize UNION query in MYSQL
I have a problem with a UNION query in MySQL. We have 10 millions players on our website and we would like to select players with a multi-criterias system. For exemple, selecting US people, men, > 35 ...
3
votes
1answer
97 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
40 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
...
2
votes
1answer
71 views
mysql: need help to optimize my query/table
I'm wondering if someone could help me optimize my tables/query to speed up a query. It is currently running ridiculously slow. I think a well-thought out index could help me. Any help would be really ...
0
votes
1answer
32 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 ...
1
vote
0answers
60 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
47 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 ...
-1
votes
2answers
60 views
strategies to get data from diff database [closed]
Problem
I have two databases user_works database and pricing database.
I want to take a join of two tables user_table which is in user_works database & rent_table which is in pricing database ...
0
votes
1answer
57 views
Database schema design help needed
I am developing a PHP application expecting millions of records both parent and children. My goal is to design an optimized database design to achieve high speed and performance.
This application ...
0
votes
1answer
38 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
34 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
183 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
72 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 ...
3
votes
3answers
134 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
2answers
69 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
36 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 ...
0
votes
1answer
98 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
2answers
152 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
63 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
66 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
76 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
40 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
62 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
171 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
107 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
35 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
46 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
83 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
217 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
136 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
107 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
195 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
60 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
84 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
190 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, ...
