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

1
vote
1answer
145 views

How to store temp tables in memory during table “optimize”?

When optimizing tables, SHOW PROCESSLIST lists it as "copying to tmp table". I'm trying to ensure the copy takes place in memory to speed up the process. I've tried adjusting the tmp_table_size to ...
4
votes
1answer
144 views

sql indexing and performance

I am using mysql5.1, i have table which has about 15 lakh (1.5 million) records.This table has records for different entities i.e child records for all master entities. There are 8 columns in this ...
2
votes
1answer
187 views

Clustered composite key high fragmentation and fill factor

I've recently made a table to hold the language preferences of my users as follow: CREATE TABLE [dbo].[systemUserLangPreference]( [systemUserID] [int] NOT NULL, [langID] [int] NOT NULL, ...
1
vote
2answers
126 views

What's the best way to translate a website [closed]

I have a website in Django using MySQL, and I have more than 4.000 entries that I want to translate. What's the best way to do it? Create a new database for every language? Create other table for ...
0
votes
0answers
71 views

explain plan ,joins and optimization

I have to join 6 tables in sql query and each of these tables have 1 join to each of there master tables . Hence i have defined 5 subqueries within a sql query but i am seeing a lot of performance ...
1
vote
1answer
814 views

How can I speed up a Postgres query containing lots of Joins with an ILIKE condition

I have an odd problem which I really don't understand. Simply put I have a join containing 4 table joins, which I believe all have appropriate indexes, but which takes a ridiculous amount of time to ...
1
vote
0answers
1k views

Truncating Table Partitions Containing Data and Global Indexes

I have to truncate a partition (392,000 records) of a table with 20.000.000 records. At the moment, I'm making a truncate table and rebuilding indexes. But the table has 37 indexes and it takes 20 ...
0
votes
1answer
72 views

How to index WHERE (start_date >= '2013-12-15')

I have a table named items, and inside the table there is a some_date column, with date as the datatype. I was wondering how can I index the some_date column with PostgreSQL SELECT "items".* FROM ...
0
votes
1answer
63 views

MySQL Query Cache - what type of queue is used?

I am wondering what type of queue is MySQL Query Cache using. When there is new query and cache is full which query is deleted? Oldest one? One with fewer number of hits? Or maybe there is some ...
2
votes
2answers
1k views

Optimized query to fetch a record from 10 million entries

A table has 2 fields | UniqueKey | TimeStamp | ------------------------- | xfsddddq | 1024125412 | | xfstttdx | 1024125413 |< | xfsdxxau | 1024125415 | Estimated, there are 10 million ...
7
votes
1answer
582 views

Is the optimisation fence behaviour of a CTE (WITH query) specified in the SQL:2008 standard? If so, where?

I see frequent references to WITH queries (common table expressions, or CTEs) acting as an optimisation fence, where the server isn't permitted to push filters down into the CTE queries, pull common ...
2
votes
2answers
2k views

Optimizing mysql queries running on 50 million rows

Is there any technique/advice in order to optimize the performance of the queries below? According to my needs, my average db size will be approximately 30mill. rows per day, so every second less, ...
0
votes
1answer
85 views

MYSQL Stock Database Optimization

I am downloading a bulk of stock data: Ticker symbol, company name, industry sector, Open, high, low, close I was wondering performance-wise if separating the ticker, company name, industry sector ...
1
vote
2answers
2k views

Optimize a MySQL Database using MySQL Workbench

I actually have a few issues. First, I found this information about optimizing: MySQL OPTIMIZE all tables? The problem is, I don't seem to have a mysqlcheck program anywhere. I'm using MySQL ...
0
votes
0answers
49 views

Optimizing PostgreSQL 9.0.2

For work we have a 8GB Linode, hosting both the Rails 3 App (Passenger, Apache) and Postgres. The app is a reading heavy app, about 75% reading, and 25% writing. (A conservative estimate would be ...
1
vote
0answers
56 views

Tagged questions

I have similar to StackExchange network that utilises tagged questions. In my web application Users also can find questions by tags. The query below returns a set of tagged questions: SELECT ...
0
votes
0answers
34 views

How can the performance of a query that has a condition on two fields of each row be improved?

I have an images table with approximately 5 million rows in it. For the purposes of this question lets say the table looks like this: CREATE TABLE `images` ( `id` int(11) NOT NULL AUTO_INCREMENT, ...
0
votes
0answers
44 views

MySQL: Large data slow reads

I have very large table with 17,044,833 Rows and 6.4 GB in size. I am running the simple query below and it takes like 5 seconds. Any ideas what optimizations can I do to improve the speed of this ...
0
votes
0answers
94 views

Opimizing Mysql

Below is a Snapshot of my Server info. And the results of mysqltuner.pl Some help with commands would be appreciated. I'm new to this and have gotten pretty far, but I'm having issue with Mysql ...
11
votes
3answers
966 views

Indexes for SQL query with WHERE condition and GROUP BY

I am trying to determine which indexes to use for an SQL query with a WHERE condition and a GROUP BY which is currently running very slow. My query: SELECT group_id FROM counter WHERE ts between ...
2
votes
1answer
220 views

Has postgresql a fast add column optimization?

When adding not-null columns with a default value - does Postgresql optimizes this operation? In case the table has n rows, a not-optimized alter-table-add-column would yield n writes of the default ...
2
votes
1answer
445 views

mysql optimize group by having count

I have 2 mysql table: table t1: CREATE TABLE IF NOT EXISTS `t1` ( `id` bigint(20) NOT NULL, `url` varchar(255) DEFAULT NULL, `title` varchar(255) DEFAULT NULL, `type` varchar(255) DEFAULT ...
0
votes
0answers
83 views

Why is adding a FK constraint slow in MySQL?

I'm doing some experimentation in MySQL 5.1.53 and I've run across a case that I just don't understand. CREATE TABLE A ( `pk` INT(11) NOT NULL, `fk` INT(11) NOT NULL, PRIMARY KEY `pk_A` ...
1
vote
1answer
74 views

Will a specific denormalization increase performance in a specific scenario?

Suppose I have a table with fields | a | b | c | d | ... | z | which contains millions of records and which will serve millions of queries a day. And the field z holds the id of some parameter which ...
1
vote
2answers
198 views

optimization of expensive subquery

The following query gets the last report (where latitude and longitude and secs are <> 0) associated with the specified units: SELECT reports.* FROM reports INNER JOIN ...
1
vote
2answers
67 views

index for OR operator with different columns

What would be best index for this kind of query: -- id is the PK create table test3(id number,name varchar2(30) not null,lastname varchar2(30) not null); select id from test3 where id=:1 and ...
0
votes
1answer
66 views

Can this where in subquery be optimized?

Currently the query takes ~ 0.2 seconds. Is this the best it gets? Because it feels rather slow for the amount of data I have in my database. I've tried creating an inner join out of it but I can't ...
0
votes
1answer
245 views

Improve Insert performance for 200 Million rows

How can the following insert statement be improved - which varies from 20mins on certain days to 70-140mins on most other days. SELECT DISTINCT od.SourceID, od.OrderID, od.OrderLineNo, ...
1
vote
1answer
207 views

Can I rely on this SQL shortcut to save a query?

Consider a table with a status field that holds one of two values: | status | set('active','default') | YES | | active | I need to know how many records are in the table, and additionally how ...
0
votes
0answers
32 views

Optimization for a dynamic M2M value field

I'm using a Configurable Product methodology in my system. Everything works great, but I have one thing I think that might strongly impact the performance in the future. This is a dynamic linking ...
0
votes
0answers
75 views

query comes in slow query when log_queries_not_using_indexes is enabled

This query comes in slow query log when log_queries_not_using_indexes is enabled. SELECT a.id, d.id AS city_id, d.city FROM booking a, sub_area_master b, area_master c, city_master d WHERE ...
1
vote
1answer
57 views

How can I optimize this query?

I'm trying to select the time field from five tables and return the oldest time. The userid field in every table is indexed. Here's the query: ( SELECT `time` FROM `eventlog` WHERE userid = ...
0
votes
1answer
88 views

Optimising an Update statemement with a very large Where clause

I have a table (TableOne) with a field called [ReviewNote], this table has more than 1.5 million record, I have to assign keywords to each record based on the content of the [ReviewNote], using ...
2
votes
1answer
296 views

Specifying columns in LEFT JOIN through subquery beneficial performance wise?

I'm trying to understand how JOINS are handled, and was wondering about the following: Imagine a big query concerning a lot of tables, I'm really looking for small optimizations as it's a pretty ...
0
votes
0answers
121 views

How do I perform a batch update on the first n matching rows?

I need to perform a large number of updates at the same time. Each update needs to only affect up to a specific number of rows even though the WHERE clause might match to many more rows. So far, I'm ...
0
votes
0answers
32 views

Which of these two queries is better - can be optimized more?

With regards to the usage of the IF statement, which is better to use? Query #1: SELECT whatever FROM auctions AS a LEFT JOIN ............... WHERE ............... ORDER BY ...
5
votes
1answer
51 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 ...
0
votes
2answers
201 views

Which is better? A join or a SELECT IN? [closed]

I have a set of ids that I extract using a join of 2 tables. Now I need some information from 2 other tables. What would normally be the best approach? So a SELECT IN using the set of ids I already ...
1
vote
3answers
994 views

GUID stored in a varchar field

I have inherited several databases that use GUIDs as PKs. Instead of all the datatypes being uniqueidentifier most are varchar(50) and some varchar(100). Fields are true GUID some where created by ...
0
votes
0answers
191 views

Alternatives for subquery

There are three tables: product_hits, products, and product_subcategory_map. I want to count the number of product_hits and for each product. There could be more than one subcategory in the ...
1
vote
1answer
1k views

InnoDB: very bad performance on INSERT/UPDATE

Lately I have really big problems with INSERTs and UPDATEs. On my website 80% of all queries are SELECTs but in query slow log there are only INSERTs and UPDATEs. My dedicated server has 32GB RAM, ...
5
votes
3answers
425 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 ...
6
votes
3answers
1k views

Optimize UNION query in MySQL

I have a problem with a UNION query in MySQL. We have 10 millions players on our website and we would like to select players with a multi-criterias system. For exemple, selecting US people, men, more ...
0
votes
1answer
62 views

Optimize query to minimize tmp copying time

I have this mega query : SELECT s.eleve_id, crs.id_cours as 'cours_id', mc.code as cours_code, s.employe_id, s.id_seance, '0' as theorique, s.statut, s.type_vehicule_id, s.type_conduite_id, ...
0
votes
0answers
89 views

Is this a normal query time for a left outer join type?

I have Employee Table - 80K records about 100 fields I have an app table - 8K records and 30 fields Then a subset of employee table also stored as table that has our divisions subset - call this ...
2
votes
1answer
328 views

Optimize table and query, aggregate by date or date-hour

I have a table storing sensor data (100M rows currently) from stations from different sites. In my case, sites may have many stations. Create table statement is as follows: CREATE TABLE sensor_data ( ...
0
votes
1answer
516 views

PostgreSQL FTS and Trigram-similarity Query Optimization

I have recently started working on PostgreSQL and I have around 12M rows to handle in which I want to apply Full Text Search. I don't have any prior experience in handling such databases. I have tried ...
3
votes
1answer
101 views

Simple SELECT takes forever, but works as a subquery. Why?

I'm running a MySQL 5.1 server and I have a MyISAM table called my_table with nearly 5 million records. Its structure is the following: ...
2
votes
2answers
505 views

What are the options of reducing tempdb latency?

Took the job of monitoring and tununing a SQL Servers 2012 Enterprise Ed. SP1 (64 bit) on Windows Server 2008 R2 Standard Ed. All of them are running inside virtual machines. One SQL Server per a ...
2
votes
1answer
43 views

General purpose view for last value in a group

I am trying to create a view to show the last value in the group. Looking for a more general purpose query. CREATE TABLE a ( id INTEGER, seq INTEGER, val VARCHAR2(16), PRIMARY ...