In the context of a database, optimisation refers to the process of the query optimiser selecting the best query plan to execute.
0
votes
2answers
32 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
93 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 ...
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 ...
1
vote
1answer
58 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 ...
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 ...
9
votes
5answers
298 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 ...
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
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
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 ...
2
votes
3answers
448 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 ...
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 ...
39
votes
6answers
8k 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 ...
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
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 ...
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
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
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 ...
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 ...
1
vote
2answers
244 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?
...
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 ...
2
votes
2answers
75 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 ...
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
...
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
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 ...
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
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
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
95 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
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 ...
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
184 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
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.
...
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 ...
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 ...
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 ...
1
vote
1answer
1k 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
99 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 ...
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 ...
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 ...
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 ...
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 ...
