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
1answer
202 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
30 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 ...
1
vote
1answer
151 views

MySQL hangs in killed state

I have a strange situation where a mysql OPTIMIZE query will never end. After killinh it, it remains in the "killed" state. Scenario to reproduce: OS: CentOS 6.5 MySQL: 5.6 Engine: MyISAM (with at ...
0
votes
0answers
58 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 ...
0
votes
0answers
98 views

expdp taking so much time

I'm doing a full export using the expdp tool. I'm using 4 dump files and parallel parameter with 4 value. The export size calculus shows 30Gb but it takes almost 4 hours for 4 Gb. I'm pretty confused ...
1
vote
1answer
56 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
81 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 ...
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 ...
2
votes
1answer
190 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
86 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 ...
5
votes
1answer
48 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
1answer
310 views

INSERT take too much time

I have in my MySQL DB a few tables Most of the time, I'm only doing INSERT in my db. I have up to 4 connections to the DB, each one inserting into a different table. There cannot be two connections ...
0
votes
0answers
156 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
772 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, ...
0
votes
1answer
219 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, ...
0
votes
1answer
58 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
67 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
209 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 ( ...
1
vote
2answers
1k 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 ...
2
votes
1answer
36 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 ...
2
votes
2answers
333 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 ...
0
votes
1answer
333 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 ...
1
vote
1answer
123 views

Need to optimize simple SELECT query for InnoDB table with 2million rows

I'd like to make this query the fastest possible, since it's called really often. SELECT login_events.id FROM login_events WHERE ( DATE(created_at) >= DATE(CURRENT_DATE) AND person_id = 1 ) ...
3
votes
1answer
3k views

Maximum possible memory usage: 16.2G (874% of installed RAM)

Why the MySQL is saying that can use about 16GB of RAM even if my server has only about 2GB? # ./mysqltuner.pl >> MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net> >> Bug ...
0
votes
1answer
77 views

Complex “activity” based query optimization

I'm working on a query in the same style as Facebook's feed on a table that currently has 500,000 rows. For now I am just looking to get the 20 newest items (new records created by a user's ...
4
votes
4answers
118 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 ...
3
votes
1answer
93 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: ...
1
vote
0answers
81 views

Query optimization, SQL Server cache query, SQL Server execution query

I have 2 queries, the results of their execution is the same, but I don't know which of these is the best in terms of performance and why. Query #1: SELECT CASE WHEN ( EXISTS (SELECT 1 AS [C1] ...
0
votes
0answers
184 views

how I can set tmp_table_size for 16GB long table?

I am posting the same question as I posted in SO, but somebody advice me to post it here. I have a 16GB big table with 8 partition. And RAM 4 GB. I am running a query - SELECT ...
0
votes
0answers
44 views

Tips for improving my particular batch processing

I'm working with mail data from the USPS, which contains 10 related tables to make up a single mailing, as well as a single table containing all of the scans that each mail piece may receive. Once per ...
8
votes
1answer
244 views

Why are the plans different if the queries are logically alike?

I wrote two functions to answer Day 3's first homework question from Seven Databases in Seven Weeks. Create a stored procedure where you can input a movie title or actor's name you like, and it ...
1
vote
1answer
69 views

MySQL geospatial query optimisation suggestions

I am new to DB Administration. Trying to sort out this query which is the heart of our application. I am searching google on similar questions, but would like an experts opinion on my particular ...
0
votes
1answer
96 views

On which fields to index? Fields queried (WHERE) or fields returned (SELECT)

I have a large MySQL database table (~1 million rows and growing) on an AWS RDS Medium instance: mysql> describe clients; ...
0
votes
0answers
64 views

is it good to add index on column used in group by clause?

I have a query like- select x, sum(y) from xyz where z>=100 group by x order by sum(y) desc; will it be faster if I am going to add index on x?
0
votes
0answers
40 views

How to make a process use all the power of the machine (MySQL process)

I need to do a serious migration of data from MySQL to MongoDB. I have dumped the actual database from its server to the Amazon AWS box. I set up a powerful Amazon AWS Ubuntu EC2 12.04.3 type ...
2
votes
1answer
133 views

Optimizing query on a partitioned table

Tried to find an answer for this, but couldn't. So bear with me and apologies if this has been answered elsewhere. It may be just me not being able to find it. Here's the setup: SQL 2008 R2 EE. Table ...
1
vote
2answers
56 views

What does enabling containment on server incur?

Having read in a book of Patrick LeBlank "Microsoft® SQL Server® 2012 Step by Step": "Benefits and limitations of contained databases As with most features, there are benefits and ...
0
votes
1answer
37 views

Oracle sql plan for a select with procedure in terms section

Is there any difference in optimizing a plan to execute any sql request in case of using a procedure from a package which checks to display row or not and in case of constructing a plain sql query ...
4
votes
1answer
209 views

SQL query filled Temdb complete Harddisk [closed]

I have a query which was earlier fine but now it is running me out of hard disk space with message that tempdb is full. My Harddisk has 90 gb of space as soon as i run query temdb runs out of space in ...
1
vote
1answer
71 views

DROP TABLE on a huge InnoDB table

I have an innodb table with over 140 million rows taking 26GB. I would like to drop this table however this is taking way too long, probably days. How can I speed up this query? processor: Intel® ...
9
votes
3answers
368 views

Optimizing a CTE hierarchy

Update below I have a table of accounts with a typical acct/parent account architecture to represent a hierarchy of accounts (SQL Server 2012). I created a VIEW using a CTE to hash out the hierarchy, ...
1
vote
2answers
161 views

MySQL (InnoDB) UPDATE SET performance with value in WHERE AND clause

If an UPDATE query is performed where the values being SET are also part of the WHERE clause, in the form of an additional AND statement on the column being set's value, is there a significant ...
3
votes
2answers
119 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
646 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 ...
0
votes
2answers
195 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
1answer
81 views

How is the number of records on a select determined as high or low by optimizer?

What is the threshold between using the index and doing an direct access to the database? I assume this decision is related to the number of index access (and subsequent access to the tables). If this ...
0
votes
1answer
166 views

Optimizing multiple MySQL sub-queries and joins for multiple runs

The query (below) that's running is taking ~0.6 sec which normally would be fine but it gets run almost 1300 times for different custids. How can I make it run faster? Setup: Mac OS X 10.7.5 MySQL ...
1
vote
3answers
610 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
32 views

Large-query optimization: given my “explain” results, what stands out?

I've got a monster query, which runs quite quick in many cases, but in some cases very much slower. The EXPLAIN output below is from one of the slow cases. I'm guessing that the slowdown is related ...
2
votes
0answers
474 views

Query optimization on a large table in PostgreSQL

I am not super experienced with databases, just have a college level course under my belt. I am trying to see if an existing query can be optimized. Currently on average takes over 300ms. There is a ...