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 (3)

5
votes
2answers
10k views

Why won't SQL Server optimize the UNIONs?

Consider these queries (SQL Fiddle): Query 1: SELECT * INTO #TMP1 FROM Foo UNION SELECT * FROM Boo UNION SELECT * FROM Koo; Query 2: SELECT * INTO #TMP2 FROM Foo UNION SELECT * FROM Boo UNION ALL ...
5
votes
4answers
4k 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
1answer
501 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
4k 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
893 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
1answer
168 views

Is it possible to get seek based parallel plan for distinct/group by?

An example from this question shows that SQL Server will choose a full index scan to solve a query like this: select distinct [typeName] from [types] Where [typeName] has a non-clustered, non-...
5
votes
1answer
2k 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 ...
5
votes
4answers
1k 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
3answers
4k views

Where column not like multiple values

I am trying to select records in a postgresql db where username is not like a list of strings. SELECT * FROM rails_db WHERE username NOT LIKE 'j%' AND username NOT LIKE '%eepy%'; The issue is there ...
5
votes
1answer
257 views

Why does the CBO not chose a lower cost skip scan without stats?

In the script below, the CBO chooses an INDEX FAST FULL SCAN over an INDEX SKIP SCAN (this is illustrated by the queries with the UNION ALL but you can demonstrate it is true with just explain plan ...
5
votes
2answers
2k 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
14k 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 ...
5
votes
2answers
142 views

Optimizing “WHERE x BETWEEN a AND b GROUP BY y” query

CREATE TABLE test_table ( id uuid NOT NULL, "RefId" uuid NOT NULL, "timestampCol" timestamp without time zone NOT NULL, "bigint1" bigint NOT NULL, "bigint2" bigint NOT NULL, "int1" integer ...
5
votes
1answer
58 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
10k views

Subqueries run very fast individually, but when joined are very slow

ypercube solved the problem. Subqueries were totally unnecessary, and the whole thing works with plain joins. It is still strange that MySQL's optimizer could not make use of my original query, though....
5
votes
2answers
403 views

if database is not shrinked does it affect backup size?

Ok my question is simple. Lets say that when i click database and hover over shrink it says it can be reduced 1 GB space. Now when i do a backup of this database, does this space also getting added ...
5
votes
1answer
727 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 ...
5
votes
2answers
987 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 "...
5
votes
2answers
113 views

Help optimising delete statement

Using SQL Server 2012 Standard - I'm running a delete on a table based on the contents of another table. It's taking rather a long time (5 hours) and doesn't seem to be optimal to me, would appreciate ...
5
votes
2answers
96 views

Will I need to create a pivot or can I alter this query

The following Query that I wrote is working to a point. The problem that I found is that in each pump, cable, etc can be listed in each of the part columns. Example: What I would like to get is ...
5
votes
2answers
1k views

MySQL queries are 10 to 100 times slower after OS reboot

I have a MySQL server running on Ubuntu. If I restart the MySQL, everything is fine. If I however restart the OS, my queries take anywhere from 10 times to 100 times as long. The only "solution" to ...
5
votes
2answers
1k 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 ...
5
votes
2answers
6k 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 ...
5
votes
3answers
408 views

Should I split this large table into three smaller tables?

I'm designing a PostgreSQL database that will contain information about photos uploaded by my users. All users will have at least one main photo and optionally one or more public and/or private ...
5
votes
1answer
203 views

Query looks optimal but logical reads is huge

I run the following (really simple) query in SQL Server: SELECT MAX(PK_Field1) FROM MainTable kh WHERE kh.PK_Field1 >= '2014-12-01T00:00:00' AND kh.PK_Field2 = 1572 AND kh....
5
votes
3answers
909 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 re-...
5
votes
2answers
2k views

SQL Server Wildcardless LIKE Optimization

In a web application using SQL Server 2008 R2 as the backend, I have several fields, that if present, add WHERE clauses to the query. If I was to have all queries generated as like clauses, such as: ...
5
votes
1answer
606 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
2k 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
4k views

PostgreSQL tree structure and recursive CTE optimization

I'm trying to represent a tree structure in PostgreSQL (8.4) to be able to query the path from the root to a given node or to find all the nodes within a sub-branch. Here is a test table: CREATE ...
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
136 views

Joining in query decomposition

I have a question about query decomposition and localization, as in here: When joining 3 tables (here EMP, ASG and PROJ) is there any specifiy order, when to join which table? Why ASG and EMP are ...
5
votes
1answer
131 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
1answer
176 views

What is the best storing device for DB transaction log?

I'm using MySQL with InnoDB storing engine. Transactional log is placed to dedicated HDD. Still as it is HDD atopsar shows big load (>50%) to this HDD because of big amount of small IOs (about 50 ...
5
votes
2answers
1k 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
294 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
719 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
974 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 defined ...
5
votes
3answers
163 views

Find the user with closest taste of mine

I trying to write in SQL the following requirement: Each user can rate as much films as they like. (1= I don't like, 5=I love it) And the system should provide to the current user a list of films ...
5
votes
2answers
279 views

How to optimize indexes on MySQL query with various sorts

I have an INNODB table levels: +--------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------------+--------...
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
2answers
3k views

Can I optimize this MERGE statement?

I am attempting a single column merge between two tables. The first table (VisitorSession) has 40,000,000 rows. The second (ShoppingCart) has 9,000,000 rows. In my development environment, the query ...
4
votes
4answers
2k views

Should we add extra 5 columns or build a separate table?

Currently we have a table with 35 fields in it. Now, we got a requirement to add 5 extra columns but these columns will not be fill always. So, I was thinking just add one column and have that id as ...
4
votes
4answers
305 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
1k 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 ...
4
votes
2answers
2k views

DBMS_REDEFINITION vs EXCHANGE PARTITION in oracle

In order to partition an existing non-partitioned table it is possible to use either the EXCHANGE PARTITION or DBMS_REDEFINITION. How to decide on which of those possibilities to choose for table ...
4
votes
2answers
143 views

Execution plan percentages don't add up to 100% [closed]

I am referring to SQL Server query execution plans to take query cost and then optimize the required things by looking at the plan. But the total of individual query costs adds up to more than 100%. ...
4
votes
3answers
16k views

Is there any command to optimize all MySQL tables one by one automatically?

I execute below command line statement to optimize a table: optimize table tablename; Is there any command or statement which can optimize all the tables one by one of the selected database?