In the context of a database, optimisation refers to the process of the query optimiser selecting an efficient physical execution plan.

learn more… | top users | synonyms

5
votes
3answers
684 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 ...
5
votes
4answers
3k views

How can I speed up “show columns” on MySQL?

My application depends on running "show columns" for certain tables. It takes about 60ms to run, whereas all our other queries take under a ms. Querying information_schema directly is even slower. ...
5
votes
2answers
1k views

Is there a way to hint to query optimizer to MySQL which constraints should be done first?

This is my current query: SELECT BusinessID as ID, 111151.29341326*SQRT(pow(-6.186751-X(LatLong),2)+pow(106.772835-Y(LatLong),2)*0.98838574205337) AS Distance from ( SELECT * FROM ...
5
votes
1answer
450 views

Optimize where clause using dates

I'm trying to optimize several queries that all use a similar pattern on one of the WHERE clauses: AND (DATEADD(DAY , ISNULL(a.[due_days], 30) + 30 , [dbo].[CalcDate]([type], ...
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 ...
5
votes
3answers
805 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 ...
5
votes
3answers
2k 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 ...
5
votes
4answers
999 views

optimizing a compare query

Suppose I have two tables, A and B, and I know that size(A) = size(B). I want to confirm that the data in both tables is the same in three given columns, suppose they are X, Y, and Z (there are no ...
5
votes
2answers
1k views

Bulk Data Loading and Transaction Log

im currently working on a project which bulk import data from flat files(csv) about 18 differents files each linking to a specific table through some store procedure. I followed the steps as advised ...
5
votes
2answers
2k 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 ...
5
votes
4answers
4k views

MySQL: Index when joining to tables not being used (Performance optimizing question)

I need to optimize the following query: SELECT /* [things omitted] */ articles.blogpost_id, articles.id AS articleid FROM blogposts JOIN articles ON articles.blogpost_id = blogposts.id WHERE ...
5
votes
1answer
54 views

Is it possible to decouple row size estimation from maximum column width?

In other words, is it possible to declare a column VARCHAR(n) and have the estimated size be something other than n/2? An example use-case might be storing URLs - you expect most to be relative and ...
5
votes
1answer
4k 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 ...
5
votes
2answers
2k 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) ...
5
votes
2answers
4k 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 ...
5
votes
3answers
639 views

Database Indexing - Maintenance Jobs

I have created a script that runs every night to rebuild & re-organize indexes based on the fragmentation, Indexes with Fragmentation > 30% are rebuilt, Indexes with Fragmentation 10% - 30% are ...
5
votes
1answer
376 views

New query slower even if it has less logical reads than the old one

I have a query in a stored procedure that must list all dates in a range and JOIN on it date from a table, if it exists for that day. The procedure was created before I was employed as the DBA, so I ...
5
votes
1answer
1k views

Schema for analytics table in Postgres

We use Postgres for analytics (star schema). Every few seconds we get reports on ~500 metrics types. The simplest schema would be: timestamp metric_type value 78930890 FOO ...
5
votes
1answer
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 ...
5
votes
1answer
69 views

Postgres: Is migrating old records to separate table and creating new aggregated records a good strategy to improve search speed?

I have a table with 1M+ records. New records are created and updated each day each time an event occurs for each type of event (which there are many). I frequently need to find sums across many ...
5
votes
2answers
155 views

Multi-row insert vs multiple single row inserts

In my app I do multi-row inserts when I can just because it reduces the number of round trips between the db and the app. However, I was curious, is there any other advantages? For example, if ...
5
votes
1answer
299 views

Determining which isolation level is appropriate

This is a homework question. For the following transactions state the isolation level that will maximize throughput without lowering the integrity of the database. Explain the answer. ...
5
votes
1answer
247 views

Walking a BTREE index as far as possible in MySQL

Suppose one has a column of words on which one builds a BTREE index: CREATE TABLE myTable ( words VARCHAR(25), INDEX USING BTREE (words) ); LOAD DATA LOCAL INFILE '/usr/share/dict/words' INTO ...
5
votes
2answers
595 views

How to use merge hints to isolate complex queries in SQL Server

I can understand that if I join individual queries that are "individually fast" the combination may become slow because the default execution plan may be non-optimal. However when I know the number ...
5
votes
1answer
784 views

Query performance while using sqlcmd [duplicate]

Possible Duplicate: Running queries with SQLCMD vs. Running queries with SSMS I have a number of scripts that query the data from different tables and consequently update them. I have ...
4
votes
4answers
1k 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 ...
4
votes
4answers
299 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 ...
4
votes
2answers
148 views

Optimizing my update sequence

Whenever a user updates his profile, we conduct a set of queries like so: DELETE FROM `user_likes` WHERE `ID` = $id INSERT INTO `user_likes` VALUES (DEFAULT, $id, $interest_id, $interest_name) Is ...
4
votes
2answers
29k 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 ...
4
votes
4answers
349 views

Do cross-joins get optimised by the client library?

Note This is deliberately database agnostic, I'm interested in how different implementations, er, differ. I came across Joins are for lazy people on SO, and it's made me question an assumption I'd ...
4
votes
1answer
473 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 ...
4
votes
3answers
11k views

I've got 16GB of ram, how should I configure MySQL Server?

I've got a dedicated production server with multiple cpus and 16GB of ram. I would like to know how can I configure it better to take advantage of the server resources for better performance because ...
4
votes
1answer
1k 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 ...
4
votes
1answer
4k views

Sql server table insert performance optimization

Setting In a datawarehouse, I am joining a fact table to 20 dimensions. The fact table has 32 million rows and 30 columns. This is a temporary staging table so I don't have to deal with other users ...
4
votes
1answer
221 views

250 billion rows - computing interconnectedness on a really huge scale

I have a table of approximately 700k rows, each identified by a unique itemnumber. Each row/item can be associated with any of the other rows/items in the table by calculating a single numerical ...
4
votes
3answers
258 views

How does the T-SQL optimizer handle “and false”

In SQL SERVER 2008 I'm thinking about a query such as: SELECT ... FROM ... WHERE ... AND @a=1 UNION SELECT ... FROM ... WHERE ... AND @a=2 Given that @a is clearly the same value, and therefore ...
4
votes
2answers
513 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 ...
4
votes
2answers
2k 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 ...
4
votes
2answers
171 views

How to create multiple entries on index based on the fields of one row?

I never found a good way to index multiple fields of one row as entries of an index or simulate this feature on MySQL. The problem arises when you have fields working as tags or similar concept. Ex.: ...
4
votes
4answers
161 views

Optimize MySQL Query using LEFT JOIN

I have a query with execution plan as below and is taking more than 20 seconds to execute how can i optimize it EXPLAIN SELECT c.coupon_id as c_id, `c` . *, `st`.`name` AS `store` FROM `coupon` AS ...
4
votes
2answers
500 views

Can I optimize for the zIIP processor?

Is it possible to change SQL in a z/OS mainframe COBOL application so that it becomes eligible to be directed to the IBM System z Integrated Information Processor (zIIP)?
4
votes
2answers
5k 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 ...
4
votes
1answer
666 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 ...
4
votes
2answers
781 views

Fetch first 9 records and make one more record which will be as “Others” in MySQL

I am fetching records from one table with count of one field with other field as name. I want only 10 records. out of which 9 records give me field and its count. but i want to show 10 record as ...
4
votes
2answers
122 views

Optimizing queries on a range of timestamps (one column)

I am using Postgres 9.3 through Heroku. I have a table, "traffic", with 1M+ records that has many inserts and updates every day. I need to perform SUM operations across this table over different time ...
4
votes
2answers
797 views

TokuDB optimize table does not reclaim disk space

Was running an optimize table command against one of my tables about 11Gig in size, surprisingly used disk space grew by 2Gig immediately after optimization process finished. I would expect the ...
4
votes
1answer
1k views

ORDER BY optimization for a large table

I am currently aggregating posts from different social networks. Currently I have 50-100 million posts from Facebook, Twitter, Youtube, Instagram , Pinterest. Consider a table posts posts { id ...
4
votes
1answer
1k 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 ...
4
votes
2answers
916 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 ...
4
votes
1answer
301 views

Optimal hash technique to index large text

Erwin Brandstetter thankfully saved me from myself when I tried to create unique indexes on textual columns with large lengths. The upper limit for the insertion rate is tens of billions of rows per ...