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
4 views

TPC-H query-18 takes too much of time

TPC-H query-18 takes too much of time. How do i optimize it. No MY.CNF settings have been changed and no indexes have been applied . Please suggest on how to get better results on MYSQL 5.6
2
votes
2answers
31 views

SUM and Group By Optimization

I use this query: SELECT logdate, SUM(bytes)/1024/1024/1024 as traffic, customer FROM `Logdata` WHERE customer is not null AND logdate >= "2016-07-01" AND ...
2
votes
2answers
205 views

Slow subquery in SELECT statement

Having tried many different methods, I can't get my query taking shorter than 40 seconds to run with a dataset of ~11000 rows. The table is a list of hotel bookings, containing (most relevant) Check-...
0
votes
1answer
42 views

Can I get an equivalent query for this Not in Query

SELECT ID FROM dbo.SomeTable WHERE ID not in ( SELECT MIN(ID) FROM dbo.SomeTable GROUP BY SystemNo ) As this query ...
2
votes
1answer
51 views

Recompile not working for DELETE statement

I want to optimize a deletion from my tables. I have a table-variable input for my usp of following type: CREATE TYPE [AddressInfoParts] AS TABLE( ( [TcpInfoId] [BIGINT] NULL INDEX ...
5
votes
2answers
279 views

How to optimize indexes on MySQL query with various sorts

I have an INNODB table levels: +--------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------------+--------...
0
votes
0answers
22 views

Increase in IO and CPU cost when explicit conversion is used in a query

I want to check the notable difference between the IO and CPU cost when explicit conversions like Convert or Cast are performed and when not as we need to take a call whether to use or not explicit ...
0
votes
0answers
18 views

query cost in postgresql using explain

I read the following link and try to understand the output. https://www.postgresql.org/docs/9.1/static/sql-explain.html EXPLAIN SELECT sum(i) FROM foo WHERE i < 10; ...
0
votes
0answers
37 views

pros and cons of partition or not-partition this sql server table that is highly hit?

I have a table that is highly hit on my database. this table has 370,370 records. when I run the following select: select [ImageSize] , the_num = count(*) from [dbo].[ProductImages] group by [...
0
votes
1answer
16 views

Using varchar_pattern_ops in a multi-column index in Postgres

I'm using postgres 9.5 If I have a table with 2 columns like so: CREATE TABLE mystuff ( somestring character varying(256), timestamp_ timestamp without time zone NOT NULL ) Will this multi-...
1
vote
1answer
64 views

Optimize mysql database performance

I have a database with some relatively small tables and one big table with about 10 million rows (11 columns) that I would like to optimize its performance. The table looks like this: Id: (int 11, ...
3
votes
1answer
46 views

Online text based game design - Am I linking the databases correctly?

I'm trying to recreate an old game that I used to play that went offline quite some time ago. I'm doing this as a hobby project for myself with the hope that maybe a few hundred people might enjoy it ...
0
votes
2answers
38 views

How to improve query count execution with mySql replicate?

For my report I have to use count queries (name 'user_activity' for example) and then group by by another column (user_type) in another table (user table). And its really time consuming when it ...
1
vote
1answer
1k views

Slow insert with MySQL full-text index

I use a full-text index in a MySQL table, and each insert into this table takes about 3 seconds. It seems that MySQL rebuilds (a part) of the full text index after each insert/update. Is this right? ...
0
votes
0answers
24 views

Optimizing query with common parts

I'm creating query that returns list of doctors, that provides a specific service. Here is schema: table user (id, name, user_access_group, is_real_doctor) table servicestypes_groups (...
0
votes
1answer
36 views

Possible to use first CTE as filter for second CTE?

I'm trying to optimize a query on a large database that essentially fetches 50 images for a site and the URL's for the pages they appear on. The current query works okay for smaller sites but larger ...
1
vote
1answer
540 views

MySQL Query with good execution plan is very slow due to Sending data operation

I have the following query: SELECT a.borrowerId AS borrower_id, a.created, NULL AS initiator_team_id, NULL AS initiator_user_id, NULL AS old_team_id, NULL AS old_user_id, ...
3
votes
1answer
231 views

How to avoid implicit conversion for an Integer column

SELECT [BusinessEntityID],[NationalIDNumber],[LoginID],[OrganizationNode] FROM [AdventureWorks2014].[HumanResources].[Employee] where BusinessEntityID = 5 I get convert_implicit() function ...
0
votes
0answers
14 views

optimize multiple match against and temporary tables

I have a table of movies filled with text data. The query is create temporary table t1 SELECT *, MATCH(keywords) AGAINST('$mk') as score1, MATCH(genres) AGAINST('$mg') as score2, ...
5
votes
2answers
140 views

Optimizing “WHERE x BETWEEN a AND b GROUP BY y” query

CREATE TABLE test_table ( id uuid NOT NULL, "RefId" uuid NOT NULL, "timestampCol" timestamp without time zone NOT NULL, "bigint1" bigint NOT NULL, "bigint2" bigint NOT NULL, "int1" integer ...
0
votes
2answers
40 views

Left Join 3 tables into ONE in Mysql

I have 3 tables: Employees, attendanceIn table, attendanceOut And My query is SELECT employees.eno,employees.ename,employees.dept,attendanceIn.attIn AS attIn, attendanceOut.attOutAS attout ...
1
vote
1answer
861 views

MySQL: Optimize query with a range and distinct count

I have a query on a large table with millions of rows that looks like this: SELECT COUNT( DISTINCT clicks.tx_id ) AS unique_clicks_count , clicks.offer_id FROM clicks WHERE ...
3
votes
1answer
77 views

Optimise a LATERAL JOIN query on a big table

I'm using Postgres 9.5. I have a table that records page hits from several web sites. This table contains about 32 million rows spanning from Jan 1, 2016 to June 30, 2016. CREATE TABLE event_pg ( ...
0
votes
2answers
114 views

Inserting 500K records into SQL Server 2008 is taking 15s; how to optimize?

I have a SQL Server 2008 machine that is running backed by a SAN (not entirely certain of the SAN configuration). I've been noticing that some queries are slow to respond, so have been running some ...
0
votes
2answers
91 views

Optimizing sql server MIN in the select blah where blah IN (SELECT MIN(blah)

is there any effective way to tune the query below? I have a filtered index on OrganisationID <> 0 Primary key is on another field, not relevant for this query ItemCode varchar(20). I have tagged ...
0
votes
2answers
5k views

Optimize IN clause in where query with order by - MySQL

I am trying to optimize a query that using IN clause in WHERE to avoid file sorting. To make it easy , I created the following sample which shows the problem. Here is my query: SELECT * FROM `test` ...
0
votes
2answers
483 views

Optimize large table design

First, I am a complete novice when it comes to databases, but have been given the task to speed up queries on a large set of data. (Hundreds of millions of records) The current implementation is a ...
0
votes
1answer
153 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 ...
2
votes
1answer
765 views

How to improve the performance of SELECT queries with multiple JOINs on a 50GB database hosted on AWS m3.medium MySQL RDS?

Example query: SELECT p.ListAgentEmail, p.ListAgentFirstName, p.ListAgentLastName, p.ListingKey FROM Property_RES AS p INNER JOIN Property_RES_COUNTIES_OR_REGIONS ON ...
2
votes
3answers
2k views

Is it worth to separate columns into multiple tables for one-to-one relational table

I need to make a decision for database structure on whether to separate one-to-one relational columns into multiple tables and link with one relationship id or just add all columns into one table. ...
1
vote
2answers
43 views

MySQL to use all cores 20 cores I have

I am currently setting up a website that uses a MySQL database having more than 40 million rows of data. I am using one server for files and separate mysql server. Two servers are having 20 core, 64G ...
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
votes
0answers
13 views

Further optimisation of sqlite

Currently I have been using C# and sqlite together. Of my knowledge I have done the index on those fields where I have the "where" query. In addition I have been reading of pragma settings. I am not ...
2
votes
0answers
37 views

Understanding Database Query Cost Optimzation

I have been learning Query Optimization from "Fundamentals of Database Systems by Elmasri and Navathe (6th edition)" and I am having trouble understanding on how some variables are being derived in ...
1
vote
2answers
140 views

Efficient workflow/queries to save unique information?

I am saving company information in a database. There is a single table with an auto-incrementing key that is unique to each company. There are separate tables for emails, websites, phone numbers, and ...
3
votes
0answers
89 views

Heavy I/O for Microsoft Transaction Log

We have a Microsoft SQL Server (SQL Server 2012 SP3), we've noticed that transaction log I/O is very high. We do have a busy server; we're hosted at Rackspace with SSD block storage. The Logs are are ...
1
vote
1answer
615 views

Optimising a query of an SSRS dataset

Consider the query given below: SELECT * FROM ((SELECT c.cust_id , c.username , REPLACE(qs.seg_type_ref_key_02, 'N/A', 'Non Vip') casino_group, ...
0
votes
1answer
53 views

Set indexes for database used for login and register actions

I am thinking on how to set indexes to improve performance of a database for register and login actions.I have seen that alphabetical indexes are not good so I don't know how to do...Can you help me?...
2
votes
1answer
7k 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
21 views

Postgres: extracting multiple bit-columns from single 'flags' column after join with 2nd table

I have a query similar to this below. As you can see, there are multiple parts which are very similar. SELECT id, aaaa, bbbb, tags ,( SELECT COUNT(*) > 0 ...
3
votes
3answers
68 views

IN Clause - listing values VS query

My app runs a lot of queries like this SELECT ... FROM Table WHERE CategoryID IN (SELECT CategoryID FROM OtherTable WHERE ..) To save performance I decided to cache the second subquery in the ...
-1
votes
1answer
70 views

Meaning of terms in SQL Server execution plan [closed]

I am finding difficulty in understanding the output of execution plan. Execution plan consist of terms like "Estimated sub tree cost", "Estimated I/O cost" etc. I can find the definition of those on ...
4
votes
1answer
37 views

What is the best way to reload data in MongoDB

I am benchmarking MongoDB using YCSB framework using the latest drivers. During the benchmark, I change the cluster setup (adding/removing shards) extensively and delete/insert workloads in the order ...
4
votes
2answers
143 views

Execution plan percentages don't add up to 100% [closed]

I am referring to SQL Server query execution plans to take query cost and then optimize the required things by looking at the plan. But the total of individual query costs adds up to more than 100%. ...
0
votes
1answer
190 views

How to load entire database into memory?

I want to load my entire database into memory, but how can I do this? I have about 256 GB memory and my database is about 200 GB, so I can easily handle in memory. When I do select count(*) from ...
0
votes
1answer
76 views

A multi-table JOIN query as subquery to a query that JOINS the same tables. Good or bad?

I have the following tables in my postgres database - power_relations - (id, properties) - power_relation_members - (id, power_relation_id, member_id, member_type) - power_lines - (...
0
votes
2answers
69 views

MySQL Optimization Suggestions for EC2 T2 Small 2GB RAM

I'm trying to optimize our MySQL configuration for our server. The reason I believe that MySQL needs to be configured further is because my log has shown that our SELECT queries are taking a long time ...
1
vote
1answer
52 views

Optimize MySQL Query with multiple subqueries

I have the following Query: SELECT p.id AS post_id, p.status, p.user_id AS post_user_id, ( SELECT pl.id FROM postlocks AS pl WHERE pl.post_id = p....
-1
votes
1answer
23 views

A test fixture database to experiment on performance tuning?

I would like to learn the basics of database performance tuning, and for myself I learn things faster if I can do hands-on to practise the learning materials. For the performance tuning, it seems ...