Questions tagged [optimization]
In the context of a database, optimisation refers to the process of the query optimiser selecting an efficient physical execution plan.
1,646
questions
2
votes
1answer
296 views
datetime index skipped for same query different params
I am running two queries with same query but different parameter and one of them is extemely slow while the other is fast. Both the results are alomost the same. (500 and 575)
Any idea why it skips "...
9
votes
2answers
4k views
Does SQL Server evaluate functions once for every row?
I have a query like this:
SELECT col1
FROM MyTable
WHERE
DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))
BETWEEN col2
AND col3
;
This gives a tooltip on the execution plan ...
0
votes
3answers
221 views
Dog slow query, needs optimizing, please help
I found this query in our web app that has been made by hired programmers. It performs like a dog and needs optimization, but I'm no MySQL wizard (yet), unfortunately. Please help, anything is ...
2
votes
1answer
284 views
Changing to InnoDB increases iostat
I have changed my MyISAM tables to InnoDB and from that day, iostat is increasing continuously. My findings on the server say that mysqld is the one who is increasing iostat.
But how can it increase ...
1
vote
2answers
318 views
Does this cause overhead ? Select name from User where user_name LIKE '%alex%'
I'm wondering if this statement can cause real overhead if many concurrent users
send the same request to mySQL InnoDB storage engine. Let's say I have a table that
has the user_name, and someone want ...
2
votes
2answers
131 views
Performing a “Spread Negitive” over a dataset
I have a situation where a table that represents outstanding balances and credits are both contained in one table. What I need to do is apply all outstanding credits (preferably in order of oldest ...
2
votes
1answer
941 views
Poor MySQL for Drupal on Shared Hosting
My Drupal site is on a shared host where I cannot modify the my.cnf configurations but the database seems to be performing poorly. Here are some of the flagged issues that I can see...
In phpMyAdmin,...
18
votes
2answers
2k views
Is it possible to give the optimizer more or all time it needs?
Given that the optimizer cannot take all the time it needs (it has to minimize the execution time and not contribute to it) to explore all possible execution plans it sometimes get cut off.
I was ...
4
votes
1answer
260 views
Optimizing queries against a set of tables that contain partitioned data
I have a third party program that creates and populates a series of tables to hold some historical data:
data_33 -- contains data for 12/01/11 - 12/05/11
data_34 -- contains data for 12/05/11 - 12/14/...
4
votes
1answer
346 views
MySQL: A better way to write this query?
We are using the following query in our system:
SELECT f.type AS type,
COUNT(i.id) AS submissions,
SUM((CASE (e.oiPost + e.oiPhone + e.oiEmail)
WHEN 0 THEN 0
WHEN ...
3
votes
3answers
166 views
Optimisation Newbie: How much of a 'sin' is redundancy?
I've been developing for a while, but never really had to deal with DB/scaling issues before. That's suddenly changed and I've found myself in the deep end.
I have 2 SQL tables, as such:
VOTES
...
8
votes
2answers
7k views
REBUILD - Clustered Index, TABLE, or both?
I'm having trouble finding a definitive resource on this anywhere, so hopefully a guru can give me an answer here.
I have a very large table that we had to add a column to. The clustered index is ...
1
vote
2answers
152 views
How to restructure this slow query
For the following query:
SELECT l.*
FROM `leads` AS `l`
INNER JOIN `users` AS `u` ON l.client_id = u.id
WHERE (`l`.`lender_id` = 1)
AND (l.claimed_date IS NULL)
AND (`l`.`disabled` = 0)
AND (u....
35
votes
1answer
16k views
USING construct in JOIN clause can introduce optimization barriers in certain cases?
It was brought to my attention that the USING construct (instead of ON) in the FROM clause of SELECT queries might introduce optimization barriers in certain cases.
I mean this key word:
SELECT *
...
55
votes
3answers
76k views
When to use views in MySQL?
When creating tables from multiple joins for use in analysis, when is it preferred to use views versus creating a new table?
One reason that I would prefer to use views is that the database schema ...
2
votes
2answers
153 views
Why isn't the following query using the `always` index?
This morning I started receiving load warnings on one of the MySQL servers I manage. SHOW FULL PROCESSLIST revealed that the culprit was the following (poorly written) query:
SELECT * FROM movies, ...
2
votes
2answers
2k views
Trying to optimize query for performance (MySQL)
So I have this table that has a lot of foreign keys but when I will a record, I need to join all on those tables because I generally need those fields for the record to make any sense. The basic ...
4
votes
1answer
1k views
Measuring the relative performance of queries given the assertion that “cost” isn't reliable
The cost field is a comparative cost that is used internally to determine the best cost for particular plans. The costs of different statements are not really directly comparable.
Source
Does this ...
1
vote
0answers
69 views
Link-table(s) vs Redundant Columns? [duplicate]
Possible Duplicate:
Would it be considered as a bad practice to have multiple nullable FKs on a table in SQL Server
I have two tables, Users and People, both of which share a common attribute, ...
4
votes
2answers
3k views
Upper or Lower Case Optimisation on MS SQL Server
Does anyone know if MS SQL Server text queries are optimised for upper or lower case strings?
I read somewhere the it was uppercase, but cant seem to find the reference to it again. Of course if ...
3
votes
1answer
146 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 ...
16
votes
1answer
2k views
Monumental difference in execution time between queries when using RECOMPILE query hint
I have two almost identical queries running on the same SQL Server 2005 instance:
The first one is the original SELECT query as generated by LINQ (I know, I know... I'm not the application developer, ...
6
votes
1answer
160 views
Are there any fully declarative implementations of SQL
I was recently bitten by a bug ( in my code ) where these two queries had drastically different runtimes:
select * from smalltable st
inner join bigtable bt on st.btid = bt.btid
select * from ...
8
votes
4answers
10k views
MySQL subquery slows down drastically, but they work fine independently
Query 1:
select distinct email from mybigtable where account_id=345
takes 0.1s
Query 2:
Select count(*) as total from mybigtable where account_id=123 and email IN (<include all from above ...
4
votes
2answers
820 views
Huge database logging of event type rows and ways to optimize it
We have a database that stores events. Events are generated at a 1000 per sec rate. We need to keep these events accessible for some years.
Usual use of these events is selecting some of them from the ...
3
votes
3answers
2k views
How to restructure this slow query containing subquery?
This query takes about 2-6 seconds to run depending which tag id(s) is/are selected:
SELECT t2.id, t2.name, count(*) as kpl FROM
(SELECT pt1.post_id
FROM posts_tags pt1
WHERE pt1.tag_id ...
7
votes
2answers
3k views
Speed up INSERTs
I need to have a way to insert to a table, fast, synchronous, with minimal duration. What I've tried ("blind") is :
Have no index on the table whatsoever
Switch to simple logging (from full logging)
...
7
votes
2answers
11k views
Fastest validation query in PostgreSQL
What is the fastest query in PostgreSQL, which I can use as a validationQuery binding a JNDI resource?
I though that SELECT 1 is the simplest, but in this document it is said that in PostgreSQL we ...
8
votes
1answer
4k views
MySQL reads / writes per table
I am optimising our DB. Essentially I am trying to find the most written and the most read tables in our db. After that I will follow by sym-linking those tables into separate drives.
Is there a way ...
1
vote
2answers
8k views
What would be the fastest approach to bulk update this mysql table (Innodb)?
I have a table with 100M rows. I am able update my table like 850 updates / minute. How to speed this up?
I am using following approach:
mysql -uroot < query_file
where query_file contains 1M ...
5
votes
2answers
9k views
Scheduled optimization of tables in MySQL InnoDB
What is the best way to schedule automatic optimization of tables in a MySQL InnoDB database? Can I use events for example? I have recently had a big performance issue (when querying) with one of the ...
3
votes
2answers
4k views
How can I optimize performance on a MySQL ndbcluster?
So, this is what I have:
A simple table classes__to_students:
|class |student|
---------------
Math Alice
Math Bob
Math Peter
Math Anne
Music Bob
Music Chis
Music ...
12
votes
2answers
4k views
How many stored procedure parameters is too many?
I've just started writing a stored procedure in SQL Server 2008 and have 30+ parameters. I've never written one with more than ~10 parameters, and that got me thinking... At what point are there too ...
1
vote
1answer
886 views
Optimization: Delete with Top and minimize the where clause date range
I have a delete statement that get run repetitive until all the data are removed.
Delete TOP (1000) From Log Where CreationDate < '2011-Dec-31' AND pKey NOT IN (...)
I am considering of ...
2
votes
1answer
1k views
MySQL Tuner OPTIMIZE and Defragmentation
I have just started using mysqltuner to help keep my mysql server fast and healthy. However, I am having a little trouble optimizing my tables. When I execute mysqltuner, it all works fine and returns ...
2
votes
2answers
2k views
In DB2, how can I disable NLJOIN?
I want to indicate to the optimizer, via SET or optimization profiles, that NLJOIN shouldn't be used at all when optimizing a query.
I've been looking for this for a while without any success. I ...
3
votes
1answer
135 views
PostgreSQL + query planner + amount of entities
I'm working on a college assignment about how the query planner uses statistics and makes the most optimal query plan.
I have read the 57.1. Row Estimation Examples article and I know how PostgreSQL ...
1
vote
1answer
2k views
At which frequency run CTXSYS.CTX_DDL.OPTIMIZE_INDEX(…) on a fulltext index?
I've been told to run periodically CTXSYS.CTX_DDL.OPTIMIZE_INDEX(...) on some fulltext indexes, with no more information, in particular at which frequency this task should be run.
I guess that it ...
8
votes
2answers
3k views
Insert-heavy InnoDB table won't use all my CPU
I have a packet log database, which is almost never queried. It just needs to be fast on inserts. I'm using InnoDB because I'd like to maintain ACID compliance, since even losing a single packet could ...
3
votes
1answer
125 views
Does combining columns into 1 column helps to optimize a large table?
I have user, items and user_items tables, and as usual user has many items.
Tables contains:
User: ~50K
Items: ~3.5M
UserItems: ~5M
I am using MySQL and most of my queries are selecting items of a ...
4
votes
1answer
6k views
Mysql Memory table getting many locks
On my site I log every pageview (date, ip, referrer, page, etc) in a simple mysql table.
This table has the following activity:
1 SELECT per minute
1 DELETE per minute
no UPDATE queries
Lots of ...
2
votes
1answer
545 views
How we can enable `index_condition_pushdown`?
I have optimizer_switch variable as
mysql> show variables like 'optimizer_switch';
+------------------+-------------------------------------------------------------------------------------------+
|...
2
votes
1answer
7k 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 ...
4
votes
1answer
2k views
How to use the METHOD_OPT parameter in DBMS_STATS.gather_table_stats
Hi there I was just wondering what would be the difference between these 2 METHOD_OPT parameter settings:
FOR COLUMNS SIZE 5 some_column
and
FOR COLUMNS some_column SIZE 5
both will create a ...
3
votes
2answers
318 views
Count() versus keeping a column with a total count [duplicate]
Possible Duplicate:
Storing vs calculating aggregate values
Say I have a table full of photos that each have an albumId that is a foreign key to an album table. To get the amount of photos in ...
1
vote
3answers
302 views
PostgreSQL and query planner
I have question about query planner in PostgreSQL. I know, that this planner is taking data from pg_statistics, but... Can anyone tell me on what basis this planner is taking those informations to ...
7
votes
1answer
243 views
On-the-fly query/command modification
I'm responsible for administrating an instance of SQL Server 200x which has the misfortune of serving as a backend to a turnkey application written with a disgusting performance problem. It repeatedly ...
15
votes
6answers
15k views
Suddenly Slow Execution Plan for Stored Proc
I'm trying to understand an issue we're having with SQL Server 2000. We are a moderately transactional website and we have a stored proc called sp_GetCurrentTransactions which accepts a customerID, ...
15
votes
1answer
5k views
Why is Clustered Index Scan Number of Executions so high?
I have two similar queries that generate the same query plan, except that one query plan executes a Clustered Index Scan 1316 times, while the other executes it 1 time.
The only difference between ...
4
votes
4answers
337 views
SQL Server Optimization/Configuration for Inflexible Application
I'm responsible for overseeing an application which uses SQL Server as its backend storage solution. Unfortunately, the application has no concept of how a RDBMS should be utilized and seems to use it ...