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,966
questions
5
votes
2
answers
12k
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 ...
- 73
3
votes
2
answers
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 ...
- 877
12
votes
2
answers
8k
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,257
1
vote
1
answer
976
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 ...
- 729
2
votes
1
answer
2k
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 ...
- 123
2
votes
2
answers
3k
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 ...
- 533
3
votes
1
answer
207
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 ...
- 131
1
vote
1
answer
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 ...
- 937
8
votes
2
answers
4k
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 ...
- 183
3
votes
1
answer
134
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 ...
- 133
4
votes
1
answer
7k
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 ...
- 205
2
votes
1
answer
749
views
How we can enable `index_condition_pushdown`?
I have optimizer_switch variable as
mysql> show variables like 'optimizer_switch';
+------------------+-------------------------------------------------------------------------------------------+
|...
- 9,627
2
votes
1
answer
8k
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 ...
- 121
4
votes
1
answer
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 ...
- 261
3
votes
2
answers
335
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 ...
- 131
1
vote
3
answers
368
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 ...
Krzysztof Trzos
7
votes
1
answer
285
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 ...
- 327
15
votes
6
answers
17k
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, ...
- 385
17
votes
1
answer
6k
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 ...
- 271
4
votes
4
answers
359
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 ...
- 327
3
votes
1
answer
2k
views
MySQL query 'going away' on executing INSERT ON DUPLICATE UPDATE statement with a 12524 character blob
I have a mysql insert on update query like so
insert into table (col1, col2, col3) values (1,2,'huge blob with 12524 chars') on duplicate key update col3 = 'huge blob with 12524 chars';
col1, col2 ...
2
votes
1
answer
493
views
In DB2, what are the alternative ways of evaluating an index's (or several indexes) benefit?
I'm trying to evaluate different approaches to analyzing the benefit of changing the set of indexes that are available. If I pick some representative queries from my workload, I can run EXPLAIN and ...
- 533
3
votes
2
answers
4k
views
How to optimize this dynamic stored procedure
I have a stored procedure which is retrieving data using 20 tables.
Sample of the procedure:
CREATE PROCEDURE GetEnquiries
(
@EnquiryDate DATETIME = NULL
)
AS
...
- 291
20
votes
3
answers
13k
views
Does "WHERE 1=1" usually have an impact on query performance?
I recently saw the question "where 1=1 statement"; a SQL construct I have used often in constructing dynamic SQL in an effort to write cleaner code (from the perspective of the host language).
...
- 333
10
votes
3
answers
5k
views
How recording of every change of a row in a database is generaly stored?
In a project I'm working on, every change to the rows in some tables of the database must be tracked for further audit or rollback. It must be easy to find who modified the row, from which IP address ...
- 693
18
votes
2
answers
98k
views
"Copying to tmp table" extremely slow
This is my example of query:
SELECT
nickname,
CASE class_id
WHEN 1 THEN 'Druid'
WHEN 2 THEN 'Necromancer'
WHEN 3 THEN 'Mage'
WHEN 4 THEN 'Priest'
WHEN 5 THEN '...
- 181
7
votes
2
answers
2k
views
Help optimizing MySQL slow query
i'm running a wordpress which needs some mysql optimization, i have a slow query and i would like to get rid of "Using temporary; Using filesort"
query:
EXPLAIN SELECT SQL_CALC_FOUND_ROWS wp_posts.* ...
- 71
2
votes
5
answers
862
views
Help optimizing query in the MySQL slow-query log
Our database is set up so that we have a credentials table that hold multiple different types of credentials (logins and the like). There's also a credential_pairs table that associates some of these ...
- 121
6
votes
1
answer
1k
views
Password hashes: Fixed-length binary fields or single string field?
I'm currently having am amusing debate with a friend, and we simply can't agree on the best method to store salted passwords in a database. The two options on the table are:
Storing the hash and the ...
- 163
15
votes
2
answers
2k
views
Optimization: Moving variable declarations to the top of your procedure
While working on optimizing some stored procedures, I sat down with the DBA and went through some stored procedures with high blocking and/or high read/write activity.
One thing the DBA mentioned was ...
- 263
4
votes
1
answer
3k
views
Why does MySQL use all of memory and goes into swap when doing lots of delayed inserts?
We have a database server running Centos 5.6 64bit 64Gb of RAM. The server is idle most of the time. However, every hour it gets a lot of DELAYED inserts. Up to 3 mil INSERTs per hour into a MyISAM ...
- 141
8
votes
1
answer
4k
views
Using FORCE INDEX
I was in a presentation recently given by a Facebook MySQL engineer and he mentioned there that using FORCE INDEX reduces I/O.
It was something to do with the MySQL Query Optimizer not needing to go ...
- 1,017
4
votes
2
answers
3k
views
Website is too slow when number of records increases?
I have developed a web application called Referral Program, It it developed by using PHP, MySQL. Users can joining the website and when they introduce new members, he/she gets a commission. The system ...
- 173
2
votes
2
answers
215
views
In MySQL, should I add an index even if the query that scans the table is only ran once a month?
Concretely, is it worth it to add an index to a query that scans the table of a mysql database,
even if that index would only be used once a month?
For example, having a users a table and then ...
- 123
5
votes
3
answers
1k
views
Does SQL server optimize or pre-parse stored procedures?
Despite what DBAs will tell you ("use stored procedures because the server will optimize them thus they are faster than ad-hoc queries"), I have heard that SQL server does not actually optimize stored ...
tenfour
4
votes
4
answers
2k
views
Where to look for a bottleneck after elimitating CPU, memory, and disk
I have a database job that runs each night to create a warehouse table. Using my new database server and SAN I have gotten the process down from 4 1/2 hours to 35 minutes. I have optimized it to run ...
- 227
3
votes
2
answers
199
views
What are the best Database Analytics Companies for analyzing several 1000s or Mio. of products,sum them together and analyze them for statistics?
We will get several thousands products in our database. The problem is that one product can have several different names e.g. "iPad 16GByte UMTS schwarz" or "iPad 16GB 3G black" and so on. But they ...
- 31
1
vote
1
answer
109
views
Should I Avoid Repeated References to Aggregate Columns in a View?
So I'm wanting to summarize the balance of the accounts in my business application. My (simplified for our purposes) data model looks like:
--Main account table that holds the single accounts
create ...
- 197
39
votes
5
answers
157k
views
Logical operators OR AND in condition and order of conditions in WHERE
Let's examine these two statements:
IF (CONDITION 1) OR (CONDITION 2)
...
IF (CONDITION 3) AND (CONDITION 4)
...
If CONDITION 1 is TRUE, will CONDITION 2 be checked?
If CONDITION 3 is FALSE, will ...
- 6,702
2
votes
1
answer
769
views
Optimising a sql correlated subquery statement
I am updating a column on tableA with data from tableB like this:
update tableA
set bKey = select bKey from tableB B where B.colB = tableA.colB
bKey has a unique index on tableB.
Which is the ...
storabjorn
4
votes
3
answers
1k
views
Improving slow queries. Slow query log attached
Below is the Slow Query Log when my .NET application was running. Please let me know how to improve the performance of the queries:
TCP Port: 3306, Named Pipe: (null)
Time Id Command ...
- 1,425
13
votes
5
answers
3k
views
Does SentryOne Plan Explorer work?
Does SentryOne Plan Explorer work as advertised and is it legitimate? Are there any gotchas or something to be concerned about?
It looks like it shows the hot path in color commpared to SSMS's ...
- 255
9
votes
3
answers
4k
views
How does Yelp efficiently calculate distance in the database?
For example, say I have a table:
Business(BusinessID, Lattitude, Longitude)
All are indexed of course. Also there are 1 million records
Say I want to find businesses closest to 106,5, for example, ...
- 1,345
5
votes
4
answers
4k
views
MySQL Query Optimization : Indexing and Pagination
I'm adding pagination to my 'latest news' php script and running in to an issue.
SELECT sid, title, time, bodytext, author, url FROM news WHERE approved=1 ORDER BY time desc LIMIT $start, $limit
I ...
Dave
5
votes
1
answer
3k
views
my.cnf values not applying - Optimizing MySQL in CentOS LAMP
I have checked for other occurences of my.cnf and I have found only one occurrence of /etc/my.cnf.
Then, I went ahead to fix in my values, e.g. query_cache_size etc.
After saving and literally ...
- 151
13
votes
2
answers
2k
views
The use of NOT logic in relation to indexes
According to Microsoft's book on database development Exam 70-433: Microsoft SQL Server 2008 Database Development:
Neither leading wildcard characters not NOT logic allow the query optimizer to use ...
- 4,522
1
vote
3
answers
249
views
Query optimization
In another question they told me that the speed of a DBMS largely depends on the optimizations of your queries. Does anybody know good documents about this? At school I only learned the basics.
For ...
- 203
4
votes
2
answers
787
views
How do I calculate working business minutes?
I'm working on business inteligence reports and one of my task is to create reports for time tracking.
I have to report on business minutes (10 hours per day) within business week (monday to friday)....
- 141
9
votes
1
answer
11k
views
Benefits of running the OPTIMIZE TABLE Query in MySQL DB Server
I would like to know what are the benefits [really practical] that can be reaped by running the OPTIMIZE TABLE tbl_name query in MySQL Server.
I checked this once and found that after this is run, ...
- 443
5
votes
1
answer
880
views
Speeding SQL queries for a large(?) WordPress database?
I am using a WordPress plugin called feedwordpress in order to run a planet like website (it takes content from other websites, with their permission) on WordPress (See it here).
The plugin is great ...
- 151