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

1
vote
2answers
290 views

Explicitly update a table after sp_tableoption “text in row” advices wanted

I have an extremely slow sql server 2000 DB with 8GB, and the cause of this is the 3 ntext columns which are in the more queried table. Table has indexes set and also the full-text ssearch index set ...
3
votes
1answer
949 views

Understanding optimisation of complex SELECT queries

I have a people table with basic contact info in. I have a bunch of criteria that can select Id values (fields called pid) from the people table. I want records that match ((criterion 1 or criterion ...
2
votes
1answer
280 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 ...
0
votes
3answers
194 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
241 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
257 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
102 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
679 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 ...
3
votes
1answer
225 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 - ...
4
votes
1answer
245 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
128 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 ...
5
votes
2answers
3k 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
138 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 ...
13
votes
2answers
18k 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
127 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
924 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 ...
3
votes
1answer
556 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 ...
1
vote
0answers
55 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 ...
2
votes
2answers
679 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
135 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 ...
11
votes
1answer
1k 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 ...
5
votes
4answers
2k 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
439 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
1k 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 ...
5
votes
2answers
1k 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) ...
3
votes
2answers
1k 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 ...
6
votes
1answer
1k 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
5k 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 ...
3
votes
2answers
4k 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
1k 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 ...
9
votes
2answers
457 views

How many Sproc 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
448 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
759 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
879 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 ...
1
vote
1answer
1k 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 ...
5
votes
2answers
2k 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
118 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 ...
1
vote
1answer
3k 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
1k 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 ...
2
votes
2answers
161 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 ...
1
vote
3answers
234 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 ...
6
votes
1answer
166 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 ...
10
votes
6answers
3k 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, ...
9
votes
1answer
2k 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
283 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 ...
3
votes
1answer
467 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
1answer
294 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 ...
3
votes
2answers
2k 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 ...
10
votes
3answers
5k 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). ...
6
votes
2answers
779 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 ...