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
1answer
32 views

Track database name in anemometer queries

Hello i have installed anemometer on our server in order to see the queries that need optimization. From the website hosted a few have a wordpress blog on them so it is hard to guess witch one is ...
2
votes
2answers
71 views

Optimizing a MySQL “IN” query

I'm trying to optimize a MySQL table for a query of the form: SELECT Col1, Col2 FROM MyTable WHERE Col3 IN('Val1','Val2','Val3',...) I've added an index to the table: ALTER MyTable ADD ...
2
votes
1answer
71 views

Slow CREATE TEMPORARY TABLE from SELECT in MySQL

I asked this on StackOverflow, but there are not many hints. So: SHOW CREATE TABLE cashadv_txn: CREATE TABLE `cashadv_txn` ( `txn_id` int(11) NOT NULL AUTO_INCREMENT, `cashadv_id` int(11) NOT ...
3
votes
3answers
211 views

Having both a integer Primary Key and a GUID/UUID collumn on same table-is this bad

I have my MySQL database having tables where on each table, we have a ID (int,auto increment) Clustered Primary key and a UUID(varchar36) collumn thats just a GUID. The ID is there just for indexing ...
1
vote
1answer
32 views

SQL Server 2008 running schema query without identifiable cause

I am analyzing the performance on login to an application and in particular making sure the database response is acceptable. In running just the simple Activity Monitor I can see that there are two ...
0
votes
1answer
35 views

Optimise and merge SELECT query results from 2 tables

I have a schema like this: And a query like this SELECT `users`.user_id, `users`.user_name, `users`.user_email, `users`.user_password_hash, meta_a.first_name, meta_b.last_name FROM ...
6
votes
1answer
233 views

Running out of disk space running MySQL query

I have been trying to create a "gaps and islands" identification routine in MySQL. See the previous question regarding setup here and computational resources required here. I increased the RAM of the ...
1
vote
2answers
109 views

Which service to use for unoptimized, slow mysql database ?

This will sound strange, but long story short. I have client running two CS Cart web shops one one dedicated server. Cs Cart is version 3.0.2 and each shop has around 2000 products. Website opens ...
0
votes
1answer
48 views

Slow query with JOIN

I have a stored procedure which runs pretty slow (6 sec) using the following JOIN: JOIN tariffs t ON (LEFT(`cdrs`.`cnumber`,7) = t.numberrange OR LEFT(`cdrs`.`cnumber`,8) = t.numberrange) Without ...
0
votes
2answers
119 views

SQL Server Estimated I/O Cost in real units

As I understand from other StackExchange posts, SQL Servers estimates are unitless meaning an Estimated I/O cost of 2.82387 doesn't refer to the time taken or disk I/O's, but a relative measure of how ...
0
votes
1answer
93 views

How to optimize MySQL SUM query even after adding index

The below query is taking 11 min to return the result set even after adding indexes and changing the table row format to Compressed. The 2 tables in the below query each have Table ...
0
votes
1answer
28 views

Optimising a query with two aggregate fields

I have a table in a MySQL 5.5 database with around 7 million records, with fields typeId, regionId, scanTime, scanId and a query that returns around 239,000 results. For every combination of typeId ...
0
votes
2answers
148 views

Force Sql server to use index seek in merge join

I am testing Merge join in sql server. I have an INNER JOIN and force the optimizer to do MERGE JOIN: ID in Personal Table is a Primary key ID in Abteilung Table is a primary key There is no other ...
0
votes
0answers
28 views

Optimize FROM clause by checking row existence

Provided that I have following type declaration: CREATE TYPE dbo.ID_TABLE AS TABLE ( id INT NOT NULL, PRIMARY KEY (id) ) and the following query: DECLARE @ids dbo.ID_TABLE INSERT INTO @ids ...
1
vote
1answer
59 views

Join external data in SELECT query

I have few (~20) names of products corresponding to an integer id that needs to be joined on products while exporting products data (using COPY (<query>) TO). The burden is that these names do ...
0
votes
0answers
94 views

Join on “poor” table

I have some data on same legacy tables that we've inherited. From those tables I need to make a query from 3 them, but the seems like the join for one of them is taking for ever! The query itself in ...
0
votes
1answer
62 views

get all the count of sub category from the product table in single query with mysql optimization [closed]

Table structure: CREATE TABLE some_table ( `product_id` int(11) NOT NULL AUTO_INCREMENT, `product_name` varchar(255) NOT NULL, `Product_model` varchar(255) NOT NULL, `product_discount` ...
5
votes
3answers
3k 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 ...
0
votes
1answer
70 views

Finding the right pattern for creating indexes,redesign query structure

I'm planning to redesign the structure of a query which uses over 20 full/left joins for fetching different data from the tables. Because the logic of the initial query was to bring data based on ...
2
votes
1answer
2k views

MySQL InnoDB page_cleaner settings might not be optimal

Seeing this note in mysqld.log: [Note] InnoDB: page_cleaner: 1000ms intended loop took 15888ms. The settings might not be optimal. (flushed=200 and evicted=0, during the time.) There seems to be ...
0
votes
1answer
47 views

Insert query optimization

Need your help optimising an insert query. I have two tables in my MySQL DB. I will be inserting into Table1. Information will be retreived in Table2. Table 2 contains a couple of million records. ...
1
vote
1answer
118 views

How to get SQL Server CPU and Memory read and write usage within two weeks?

Is there any a quick way or quick script on how to get the CPU and Memory usage within a time range (at least two weeks)? I did my research earlier and it seems that the procedures available seems ...
0
votes
0answers
58 views

Mysql FULLTEXT index not used in Many to Many table join search

I've got a set of data in the Books table, with BookID as the primary key. I've also got Subject and Location tables, each of which is joined to the primary Books table with a Many to Many table. ...
2
votes
1answer
76 views

MySQL performance problem

I have the following medium size table (less than 3M rows) on a 1 core, 8GB RAM server: +-------------+----------------+------+-----+---------+-------+ | Field | Type | Null | Key | ...
0
votes
1answer
88 views

Better ways than rownum limits on a union all for fallback-style queries?

I have three queries that generates some "reference" statistics and should be tried in order until one returns a result. These are currently coded as three separate calls to the DB, but I want to ...
-1
votes
2answers
236 views

Is there a way that I can merge the following update queries into one?

Is it possible to merge the following update queries into a single query? UPDATE tag.ItemTagValue SET [Value] = @firstNode WHERE FkTagID = 6 and TagIndex = 1 ...
0
votes
1answer
30 views

Exhaustive SQL stored proc validation make insert slow

So basically our team is working on a module to retrieve rows value from excel file and insert it during batch job. The requirement are as follow: - Exhaustive validation should be done for each ...
1
vote
1answer
57 views

Optimizing query in PostgreSQL that tries to match a string and matches a timestamp range

I am building a database in PostgreSQL for financial data, where the table looks like this: create table fin_data( brokerid text, stock int, holding bigint, stake float, value ...
0
votes
2answers
103 views

PostgreSQL How to optimize a query with ORDER BY and LIMIT 1?

I have the following PostgreSQL schema: CREATE TABLE User ( ID INTEGER PRIMARY KEY ); CREATE TABLE BOX ( ID INTEGER PRIMARY KEY ); CREATE SEQUENCE seq_item; CREATE TABLE Item ( ID ...
1
vote
0answers
15 views

Variables for Merging MySQL InnoDB Tables

I have several thousand InnoDB tables in MySQL 5.6 that I am trying to merge together into a single table. A simplified form of the queries I am executing (one for each of the tables to merge) is ...
1
vote
2answers
90 views

simple SELECT EXISTS takes about two hours to execute

I have mysql server hosted. Now, If I issue simple SQL statement SELECT EXISTS (SELECT * FROM ORDERS WHERE USER_ID='c85bc9f7-704f-418e-98af-a1528622dd42' AND PLACE_ID='2' AND ...
0
votes
0answers
36 views

Rewrite a Stored Procedure

i would like your help in the best way to tune and re-write a stored procedure. it is consuming a lot of time and don't know what should i do to make it faster CREATE PROCEDURE [dbo].[xxxxxxxxxxxx] ...
0
votes
0answers
28 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
57 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
52 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
37 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
81 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
53 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
50 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
101 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
68 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
39 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
31 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
600 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
75 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
33 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
293 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
32 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 ...