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
votes
1answer
268 views

How un-clustered is a CLUSTER USING table in PostgreSQL

I have some tables which benefit greatly from CLUSTER ON/CLUSTER USING in Postgres SQL: # CLUSTER table USING index_name; # ANALYZE VERBOSE table; # CLUSTER VERBOSE; A maintenance task periodically ...
2
votes
1answer
603 views

How to get rid of clustered index scan?

I have a query that was generated by C#/Linq-To-Entities code. As with this kind of query, it looks seriously ugly and I doubt it would help to include it here. But I have run it through a query ...
1
vote
1answer
262 views

inserting large quantity of data into mysql table

I need to insert about 400 000 000 rows of test data into the InnoDB table. Table is quite simple: CREATE TABLE IF NOT EXISTS `tgt` ( `id_1` int(10) unsigned NOT NULL, `id_2` int(10) unsigned NOT ...
1
vote
0answers
46 views

Querying number of tries since last successful log, checking IP as well as user

I asked this question before on stackoverflow, but it might fit better here, since it is mainly about database performance. The actual question With performance in mind, how would you go about ...
-5
votes
1answer
821 views

Recommendation for mysql please :MySQLTuner [closed]

>> MySQLTuner 1.3.0 - Major Hayden <major@mhtx.net> >> Bug reports, feature requests, and downloads at http://mysqltuner.com/ >> Run with '--help' for additional options ...
1
vote
0answers
81 views

Significant difference between duration for querys in SHOW PROFILE and SHOW PROFILES?

I have use mysql profiler to optimize an opencart query which takes more than 10 seconds to executes. mysql> SHOW PROFILES; ...
4
votes
1answer
218 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 ...
0
votes
0answers
261 views

How to improve performance of large table in AWS MySQL RDS instance?

I am unfortunate enough to have a somewhat large, unoptimized table: +-------+--------+-------+------------+---------+ | rows | data | idx | total_size | idxfrac | ...
1
vote
1answer
343 views

Optimizing GROUP BY Query

I'm running the following query: SELECT `type`, MAX(`time`) AS `last_updated` FROM `stocks` GROUP BY `type` On the following table: CREATE TABLE `stocks` ( `id` int(11) NOT NULL AUTO_INCREMENT, ...
57
votes
6answers
30k views

How to determine if an Index is required or necessary

I've been running an auto-index tool on our MS SQL database (I modified a script originating from Microsoft that looks at the index statistics tables - Automated Auto Indexing). From the stats, I now ...
0
votes
2answers
82 views

Optimize query with indexes

The following query is looking for all towns that are between 5 and 10 kilometers from a hard-coded latitude/logitude (=another town actually). I have 37010 towns. I'm using Symfony 2 which "creates" ...
0
votes
2answers
766 views

How can I speed up this query on table with 65 million records

select hash_id, hash_value, substring(T.cri, 1, 7), substring(T.cri, 9, D2.Pos-9), substring(T.cri, D2.Pos + 1, D3.Pos - D2.Pos - 1), ...
5
votes
1answer
428 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], ...
-2
votes
0answers
198 views

How to make make my query run fast? [closed]

How to make this query run faster? Table contains over 2 million rows of data and takes around 30 to 120 seconds to process the query. SELECT INET_NTOA(blocks.startIpNum), ...
2
votes
3answers
3k 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?
5
votes
1answer
332 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 ...
0
votes
1answer
56 views

Design for hierarchical embedded database

The goal is to construct a hierarchical database of Objects having the following format: TypeA -> ObjectA [values, contains ObjectB*, ObjectC*, etc.] TypeB -> ObjectB [values, contains ...
1
vote
1answer
180 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
155 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
199 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
144 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
75 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
950 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
2k 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
75 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
68 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
2k 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
603 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
101 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
3k 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 ...
1
vote
0answers
57 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
97 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
1k 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
258 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
515 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
97 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
76 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
224 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
69 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
67 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
256 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
33 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
76 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
94 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
327 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
139 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 ...