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)

0
votes
0answers
30 views

Enormous MySQL 5.5 Handler_read_rnd_next

The value of Handler_read_rnd_next is actually 91,3 G on my Database server but I am pretty sure that all tables have proper indexes. I am 100% sure that there is no query with a WHERE clause that ...
0
votes
0answers
68 views

Postgresql configuration for tiny server and append-only data

Currently my database is runing on tiny-sized hardware (digital ocean 5$) and I want to keep this hardware. It is: 512M memory 1 CPU My database works with these conditions: Data is never ...
0
votes
0answers
53 views

MySQL query hanging while copying to tmp table

I have a query that ran in 10+ seconds last week, but now is hanging even after 10 minutes. I'm running this in a test environment so the data hasn't changed much and I am baffled at what could be ...
0
votes
0answers
41 views

Why is one table taking up ~75% of time for DELETE

I have a table (Period) linked to about 6-7 other tables. When I DELETE a period, a CASCADE DELETE is used to delete the related records in the other tables. Two of the tables (Result, Amount) have ...
0
votes
1answer
84 views

MYSQL join on subquery optimization

This is a distilled version of the query that I am trying to run: SELECT * FROM table1 t1 INNER JOIN (SELECT * FROM table2 WHERE foo = 'bar') t2 ON t1.id = t2.id; The above query is incredibly ...
0
votes
1answer
57 views

Optimal design for 90mm row clickstream MySQL

Question Which optimizations should I employ to make this DB manageable? I've read a lot of posts about optimizing big dbs, but I'm pretty new so it's difficult to determine which techniques are ...
1
vote
1answer
66 views

How can I find out what stats objects were used during a SQL Server 2014 query?

I’ve read here that trace flags 3604, 9292 and 9204 enable debugging output that shows what statistics objects were used by the cardinality estimator during query compilation in SQL Server 2012. They ...
0
votes
1answer
117 views

Optimizing queries vs. adding indexes

I have this very old and SLOW query that I am trying to optimize, but I am not sure I can do anything to it, but add more indexes on columns involved in WHERE, JOIN and ORDER BY. Query: SELECT TOP ...
0
votes
1answer
147 views

What's the biggest bottleneck (time) in writing data to a SQL database?

I'm running an ETL process that is writing about 2 million rows to a SQL Server database. I'm trying to optimize the time it takes for pure inserts (I guess updates is a different story). I'm ...
2
votes
1answer
73 views

Why OUTER APPLY forces scanning the entire table?

I have a query which does not performs well and optimizer scans all 108864 items from table ExternFile uselessly when seeking only 30 rows would be fine. I have simplified the problem to following ...
2
votes
1answer
44 views

Execution plan depends on where output columns are concatenated

This query: SELECT em.Value ||'|'|| CAST(acc.FIRST_ACT_DATE AS DATE) ||'|'|| null||'|'|| spt.PROD_CODE_ID FROM lc_account_tbl lat INNER JOIN event_mapping_tbl em ON em.ACCOUNT_FK = ...
0
votes
2answers
35 views

mySQL efficiency with inner join queries

I have two tables, both with about 1,000,000 rows. Here is the question I am trying to get mySQL to solve: return all record_ids from table_a where that id matches the cat_id in table_b, but only ...
4
votes
5answers
1k views

How to optimize very slow SELECT with LEFT JOINs over big tables

I was googling, self-educating & looking for solution for hours but with no luck. I found a few similar questions here but not this case. My tables: persons (~10M rows) attributes (location, ...
2
votes
2answers
102 views

Does my table need a primary key and clustered index change?

I have a table holding 1.7 Milion rows. Definition: CREATE TABLE T( [ID] [uniqueidentifier] NOT NULL, [AID] [uniqueidentifier] NOT NULL, [BID] [uniqueidentifier] NOT NULL, [iType] [int] NOT NULL, [...
1
vote
1answer
109 views

Create index clustered X NonClustered with include

I have a table like : create table x (a varchar(20), b varchar(30), c varchar(30), primary key clustered(a asc)) so I'm wondering if I create an index like: Create nonclustered index test on x(a) ...
0
votes
0answers
42 views

What is the fastest way to do a REGEXP find and Replace in MySQL database?

I have a table with 700,000 entries and I need to check each entry for 1,000,000 words and then replace the word if found from hello to #~hello~#. Words can occur multiple times in an entry and need ...
5
votes
3answers
406 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 ...
0
votes
0answers
36 views

How to Normalize dump table, Perform-query, Perform-database Mysql

I need to migrate information hosting provider to our own systems, in this context I pose the following: The vendor provided us with history of transactions totaling approximately 7.5 million ...
0
votes
3answers
69 views

How to optimize SQL with WHERE and ORDER BY

I have the SQL statement: SELECT erp_orders_id FROM erp_orders o WHERE o.orders_export_time >= '2015-09-20' ORDER BY o.erp_orders_id ASC LIMIT 1 where erp_orders_id is primary key of type int. ...
3
votes
1answer
190 views

How to run OPTIMIZE TABLE on a 3-node cluster?

I've my hands on a 3-node Percona XtraDB Cluster where, according to mysqlcheck, some tables are corrupted (some indexes contain the wrong number of entries): mydb.mytable Warning : InnoDB: Index '...
1
vote
0answers
92 views

Queries which return more than 100k rows

I have a following query SELECT * FROM table_name WHERE #3_column BETWEEN start_date AND end_date which is taking about 10 seconds on my computer to execute and returns about 180k rows. Table schema ...
0
votes
0answers
59 views

Slow query performance - aritmethic operators, not exists, subselects

In our application we have this query that takes A LOT of time to execute, considering that is frequently used (> 20 seconds). We use Hibernate as our ORM. All the tables have a clustered primary ...
1
vote
2answers
47 views

Performing efficient searches over two columns

Disclaimer: I am extremely inexperienced with databases, so forgive me if this is an ignorant question. I have a table which contains several columns, but the ones that are important are the columns ...
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 ...
-1
votes
3answers
84 views

Looking to improve query performance

This is my query: select S-Num.Title, jobs.JobNum, jobs.JobPart, Part.PartNum, jobs.RevisionNum, jobs.Seq, jobs.Parent, Part.PartDescription, ...
9
votes
1answer
149 views

Blocked sessions waiting with PAGELATCH_* wait types?

Edited : Why are sessions reporting being blocked but waiting with PAGELATCH_*, and not LCK_M_ related wait types? I previously assumed that SQL server would only report blocking sessions in the ...
2
votes
1answer
27 views

what monitor before applying changes on the server? - looking for a basic answer - starting point only

these settings were decision of our development team: there are 2 settings: 1 - max degree of parallelism 2 - optimize for ad hoc workloads On OUR_DEV_SERVER: 1. Run the following script: --//...
1
vote
1answer
62 views

storing as smallint, querying as int

A multi-billion-row fact table in our database has 10 measures stored as int columns. The value ranges for some of these columns won't ever be above the +/-32K range of a smallint. To save I/O, we're ...
1
vote
1answer
37 views

How do I compare all tables with nonzero rowcounts (in an archive db) with tables of the same name in the line of business db?

(Full Disclosure: I am a software tester who is out of my depth. Because I am unable to get a definitive answer from the developer due to the necessary independence of the test team, I have to figure ...
0
votes
2answers
101 views

How can I defragment tables in MySQL?

I have a database named COUNTY. COUNTY has a fragmented table GEO. I executed OPTIMIZE TABLE GEO against the GEO table, but this returned the error "Table does not support optimize, doing ...
2
votes
1answer
37 views

Excluding rows by lots of ids

I need to exclude some rows that the user has already seen. Currently I am doing this by simply comparing the ids that the user has seen to the row's ids by running this query (there are also other ...
1
vote
1answer
1k views

MySQL UPDATE query stuck on “Sending Data” State

I am having difficulty getting an UPDATE query to successfully execute, due to the process getting stuck on "Sending Data" state. I feel this differs from similar questions about the "Sending Data" ...
0
votes
0answers
37 views

Query performance tuning with joins on views with large data

I've this query in oracle. This is basically the query of a BA report which I'm trying to tune. SELECT DIM_PROJECT_OL.PROGRAM_ID, DIM_ANALYTIC_STRUCT_ACCOUNT_OL.STRUCTURE_ID, SUM(NVL(...
0
votes
1answer
57 views

Is there any better way in oracle to enable parallel other than this?

I want to do do following set of operation. execute immediate "alter session enable parallel query"; execute immediate "alter session enable parallel ddl"; execute immediate "alter session set ...
13
votes
2answers
326 views

Why do SQL Server row estimates change when I add a join hint?

I have a query that joins a few tables and performs pretty badly - row estimates are way (a 1000 times) off and Nested Loops join is chosen, resulting in multiple table scans. The shape of the query ...
1
vote
1answer
2k views

Mysql Optimization suggessions

I have an InnoDB database table with more than 2 million rows. I am running efficienty indexed queries to reduce server load, but site becomes slower day by day. I ran Mysqltuner.pl to identify any ...
2
votes
1answer
744 views

Help required with query (join) optimization

I am working with a fact table source query and I have observed that performance of the query is pathetic. It has increased from 1:00 min to 6:30 min by just using a function in the select clause ...
1
vote
2answers
1k views

How to lower “Created_tmp_disk_tables” - temporary tables count created on DISK?

I have a ubuntu, mysql 5.6 DB that is heavy used by thousands of users. After 10 days of running I have this values: | Created_tmp_disk_tables | 894170 | | Created_tmp_files | 26068 | | ...
1
vote
3answers
137 views

Why does the query plan still sort a table despite having an index sorted on the columns?

I'm using Postgres 9.1 I have two tables I am joining: wikidb=> \d page Table "public.page" Column | Type | Modifiers ---------...
1
vote
2answers
173 views

Multiple-View Query Optimisation

We have a monster view that contains joins to many (many) tables in order to return the data that is required. In some cases, to get a column of information, we've have to join onto 4 tables, just to ...
2
votes
1answer
52 views

Tuning a query with: index, window function?

Description Environment: Centos 6-x64, Postgres Plus Advanced Server 9.3. I have a table with 4 columns with data as below: id code_id effective_date name 24 12 ...
12
votes
1answer
119 views

Why do these similar queries use different optimisation phases (transaction processing vs quick plan)?

The example code in this connect item Shows a bug where SELECT COUNT(*) FROM dbo.my_splitter_1('2') L1 INNER JOIN dbo.my_splitter_1('') L2 ON L1.csv_item = L2.csv_item Returns ...
2
votes
1answer
107 views

Understanding performance of highly unique WHERE clauses

I've been struggling to understand how to deal with a specific type of performance issue that shows up very often in this scenario: when you are wanting to apply multiple filters in a query, but you ...
2
votes
1answer
294 views

Two updates cause a deadlock

I have a mysql queue with the following sheme: CREATE TABLE IF NOT EXISTS `media` ( `mid` int(11) NOT NULL AUTO_INCREMENT, `order_type` int(11) NOT NULL, `media_id` varchar(50) COLLATE ...
3
votes
1answer
147 views

How to tune the query on all_constraints in Oracle 12c?

How to tune the following query, as it's taking around 8 seconds: select constraint_name,table_name from all_constraints where r_constraint_name in ...
1
vote
1answer
75 views

Query optimization in case of TIMESTAMPDIFF

I have a table where I need to delete all rows older than 5 hours. Currently I'm doing: delete from my_table where TIMESTAMPDIFF(HOUR, from_unixtime(round(created_on/1000)), now()) > 5 The ...
1
vote
1answer
300 views

Is there a way I can shorten the below query into a single query?

I have the below query. IS there a way I can put this into one single query? If not, can I shrink it further anyway? Please advice. DECLARE @highRegion TABLE(regionId INT, countR INT) DECLARE ...
11
votes
2answers
922 views

Will a WHERE query do checks on simpler comparisons (i.e. bit) before running more arduous comparisons (i.e. varchar)?

If I write a query that includes a compound WHERE clause, e.g.: SELECT * FROM MyTable WHERE BitField = 1 AND VarcharField = 'asdf' and the inclusion of that bit comparison simply excludes the ...
0
votes
3answers
224 views

Optimize mysql --> 'show global status'

I am working in the optimization of a MySQL Wordpress database running in a RDS instance at Amazon AWS. The instance is a t2.medium machine with 4GiB of RAM and 2vCPU. The size of the DB in a sql file ...
1
vote
1answer
34 views

Database: what design to have a “document table” referencing others tables

I have some tables which can have 0..* documents. How can I design my data model to avoid having a specific document table for each of my tables? Feel free to suggest by editing the data model