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)

1
vote
1answer
298 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
198 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
33 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
3
votes
0answers
205 views

Form builder schema for SQL database?

There are many ways one can envision the solution, from the top of my head: Two table approach: Field table with (name, field_name, type, description, meta) attributes Form table with (name, ...
2
votes
2answers
92 views

MariaDB server with 80K-100K DataBases

BACKGROUND : We have an environment where there is a new DB created almost every minute and it will be Dropped after few days or weeks (maximum 1 month) such that the space used is about 300GB~400GB ...
0
votes
2answers
199 views

Optimization of variables in my.cnf (mysql) for Drupal

I just get a Dedicated Server running UBUNTU and I need to optimize the variables in my.cnf for Drupal 7. I understand that this is tailed job. Then Is there any tool to help me to do this? I am a ...
0
votes
0answers
105 views

How come MySQL LIKE is faster then FULLTEXT?

So, I was trying to optimize my MySQL queries, but found out that i'm actually doing this wrong. I've changed my query from using SELECT * FROM test WHERE tst_title LIKE '1%' To: SELECT * FROM ...
1
vote
3answers
70 views

Is it safe to run optimze on mutliple tables in parallel in mysql (percona)

I have a slave on which no query is coming. Lot of data has been archived and deleted from almost all tables. Size of tables vary from 80Gb to few Mbs. And there are around 130 such tables. Mysql ...
3
votes
1answer
106 views

4m Rows in MySQL, ~5 Minutes to Run Query

I have 2 tables, a users table and a log table which literally logs each time a user signs in. What I'm trying to do is grab a list of all users with a certain rank who have not logged in for over a ...
1
vote
2answers
76 views

How to optimise MySQL query that uses dependent subquery

I have a web app that has a custom feed of images. The image queried are based on whether the users have previously seen the image and ordered by a Hacker News style score. There are only 1000 rows ...
0
votes
1answer
42 views

Is it advisable to asynchronously update the summary table every time a transaction is deleted?

Given three tables: (a) Product (b) Transaction Head (c) Transaction Detail We need to create a query that fetches a list of products with associated latest ordered quantity and latest transaction ...
2
votes
2answers
54 views

Optimizing query using a WHILE statement

I'm trying to convert a view that extracts a yearly amount into something more WHILEish The query is more or less: SELECT * FROM (SELECT SUM(EmployeePaid)*2 AS Total2013 FROM TblRecords WHERE FYear ...
2
votes
1answer
92 views

How to merge partitions in Postgres?

I have a table that is partitioned by date on a monthly basis beginning January 1, 2014. There is significantly more rows of data in 2015 than there were in 2014 and the data in 2014 is not accessed ...
5
votes
1answer
112 views

Joining in query decomposition

I have a question about query decomposition and localization, as in here: When joining 3 tables (here EMP, ASG and PROJ) is there any specifiy order, when to join which table? Why ASG and EMP are ...
3
votes
2answers
94 views

How does TOPN influence a query with group by?

I have a query that looks like the following: Select t1.field1, t2.field2, sum(t1.field3) From t1 inner join t2 on t1.id = t2.id Group by t1.field1, t2.field2 It's simplified, normally there are a ...
2
votes
2answers
135 views

MySQL: memory requirements for django app with 1 big table (460M rows)

I have created a django app that is running on virtual machine. All db tables are rather small (up to 1M rows) but recently I was asked to add a big table (460M rows). CREATE TABLE `genescorrelation` ...
5
votes
2answers
96 views

Will I need to create a pivot or can I alter this query

The following Query that I wrote is working to a point. The problem that I found is that in each pump, cable, etc can be listed in each of the part columns. Example: What I would like to get is ...
0
votes
1answer
126 views

Oracle 11g PGA advice view is empty when it shouldn't

I have the following situation in an Oracle 11g server (it's part of a RAC). PGA_AGGREGATE_TARGET is set, STATISTICS_LEVEL is TYPICAL, and yet V$PGA_TARGET_ADVICE shows up empty. Why does this happen? ...
7
votes
1answer
195 views

Which is more efficient b+ tree or search (binary or linear)?

SELECT A FROM T WHERE M > 1000 AND M < 5000; I am unable to judge which of the below is best suited for the above query: Linear search Binary search on M B+-tree with index on M B+-tree with ...
-1
votes
2answers
82 views

Optimize query or split in two queries and process result separately?

I have this DB schema: As you may notice already there is no relationship between tables and won't be on the future. If I run the following queries I will get a lot of rows per table: SELECT ...
2
votes
2answers
386 views

Why the WHERE clause is not pushed down in the view's query?

With Postgres 9.4, I'm doing the following query quite often: SELECT DISTINCT ON(recipient) * FROM messages LEFT JOIN identities ON messages.recipient = identities.name WHERE timestamp BETWEEN timeA ...
2
votes
1answer
171 views

How can I optimize searching a column for a sub-string in SQL Server?

I have a very basic table: CREATE TABLE [obj_local] ( [obj_id] INT NOT NULL, [value] NVARCHAR (1000) NOT NULL ); This table stores a lot of data, and I need to search ...
4
votes
2answers
2k views

Does an empty column value occupy same storage space as a filled column value?

I have a table with 2 columns. The type of both columns is set to varchar(38). If I create a row with an empty value for one of the columns, will it take same storage space as if the value was not ...
12
votes
3answers
320 views

Predicates: in the WHERE clause or the JOIN clause? [duplicate]

Looking at "SQL Server 2008 Internals"1 by Kalen Delaney, on page 13, it states the following: "The first step in producing such a plan is to normalize each query, which potentially breaks down a ...
0
votes
0answers
126 views

Performance Issue on Centos Server (Mysql)

I have performance issue on my centos server (vps). I asked about it in serverfault and I was said there is a problem with mysql and ask here. Here is serverfault topic, you can find details. ...
1
vote
1answer
70 views

Is smallest to largest the rule of thumb in a multi-table JOIN?

Given this query, SELECT a.* FROM a LEFT JOIN b ON b.a_id = a.id LEFT JOIN c ON c.a_id = a.id HAVING COUNT(DISTINCT b.id) < COUNT(DISTINCT c.id) if b is a significantly larger table than c, will ...
1
vote
1answer
424 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, ...
2
votes
1answer
61 views

How to optimize the sort in Postgres query

I've been trying to optimize the sort in the following query. I ran EXPLAIN ANALYZE and the majority of the time is during the sort when it arranges the output by distance. I've tried converting the ...
1
vote
0answers
11 views

PostGIS Intersects Query takes lot of time to get the result

I am developing a webmapping application in which I used PosgreSQL with PostGIS plugin as my DB. I have this two tables namely; evidensapp_polystructures and evidensapp_seniangcbr. Both tables ...
12
votes
2answers
1k views

Why is table variable forcing an index scan while temp table uses seek and bookmark lookup?

I am trying to understand why using a table variable is preventing the optimizer from using an index seek and then bookmark lookup versus an index scan. Populating the table: CREATE TABLE dbo.Test ...
0
votes
2answers
97 views

How to improve perfomance or Query Execution Time in MySQL?

This is my query: SELECT a.z_companyid_pk "z_companyid_pk" , a.company_name "Client" , a.display_name "Display_Name" , ...
1
vote
0answers
48 views

Oracle tuning strategy for columns with long tails

We have an Oracle 11g database, where the two biggest tables are Customer and Order. The order profile of our customers has a long tail: we have millions of customers, with most only having a small ...
3
votes
1answer
99 views

MySQL query WHERE IN + ORDER BY, which EXPLAIN is better and how to avoid filesort?

This is my table definition, without any special index for now: CREATE TABLE `filter` ( `field_name` varchar(32) COLLATE utf8_unicode_ci NOT NULL, `category_id` varchar(32) COLLATE ...
3
votes
2answers
230 views

SQL server tools to identify query optimisations and missing indexes [closed]

I have a broad question regarding SQL Server that was given to me on a quiz and I am not sure what the answer is as I do not have access to SQL Server at home. My question is how can I utilise SQL ...
1
vote
1answer
87 views

Int comparison very slow in query

I have this for simplicity fictional table which explains my problem: CREATE TABLE IF NOT EXISTS `posts` ( `pid` int(11) NOT NULL AUTO_INCREMENT, `user` int(11) NOT NULL, `posts` int(11) NOT ...
2
votes
0answers
120 views

Two queries usually run fast but get slow when joined

There are those 2 tables CREATE TABLE IF NOT EXISTS `media` ( `mid` int(11) NOT NULL AUTO_INCREMENT, `user` int(11) NOT NULL, `order_type` int(11) NOT NULL, `media_id` varchar(50) COLLATE ...
0
votes
0answers
200 views

optimizing paging in a stored procedure

I have the following SQL command which is built inside a stored procedure according to input parameters: SELECT DISTINCT * FROM ( SELECT DISTINCT TOP @displayLength * FROM ( ...
-3
votes
1answer
50 views

PostgreSQL - what do tms and mt mean in a SELECT query? [closed]

I'm trying to see if I can optimize the following query which was written by my predecessor: SELECT tms.*, 0 as kyori , (power( (139.745069 - ...
1
vote
0answers
19 views

Optiming ACT_RU_JOB Query

We're using Activiti version 5.15.1 with timers in our workflow which sends reminders to users who are in the workflow. Problem is that we're creating jobs in ACT_RU_JOB table and even though there ...
0
votes
1answer
66 views

NC Index not being used [closed]

I have a table (9mm records) with a field called filename nvarchar(max)(I didn't decide on this datatype and can't change it). I'm wanting to count how many records meet the following criteria: ...
0
votes
2answers
150 views

Multiple WHERE IN on multiple columns

I am not very experienced with sql queries and looking for an efficient way to perform the same WHERE IN condition on multiple columns. More specifically, i'm doing the following: SELECT a, b FROM ...
7
votes
2answers
3k views

nvarchar(max) converting to varchar and table optimization

I am working with a table that has all character types set to nvarchar some of them are nvarchar(max). We are converting all these to varchar and specifying a character width based upon the actual ...
8
votes
1answer
170 views

Overcome MERGE JOIN(INDEX SCAN) with explicit single KEY value on a FOREIGN KEY

Added 7/11 The issue is deadlocks occur because of index scan during MERGE JOIN. In this case a transaction trying to get S lock on the whole index at the FK parent table, but previously another ...
2
votes
1answer
81 views

MySQL Filesort/Order By Slow

I've been struggling with a query for the past few weeks and it now just appears as gobbly-gook to me when I try to optimize. The query runs - at about 2.3 seconds, but I know there has to be a more ...
5
votes
2answers
4k views

How to partition existing table in postgres?

I would like to partition a table with 1M+ rows by date range. How is this commonly done without requiring much downtime or risking losing data? Here are the strategies I am considering, but open to ...
5
votes
1answer
125 views

Postgres: Is migrating old records to separate table and creating new aggregated records a good strategy to improve search speed?

I have a table with 1M+ records. New records are created and updated each day each time an event occurs for each type of event (which there are many). I frequently need to find sums across many ...
0
votes
0answers
31 views

Optimization for query n increase performance

I am executing the following query: select count(inspection_result) from table where (inspection_time between "2015-06-28" and "2015-07-04") and inspection_result= 1 and part_type = "39000048" and ...
1
vote
1answer
104 views

Dynamic Partition Elimination when using “OR ” or “IN”

Say I have a query like this: SET @ContainerId = 5 SELECT * FROM Container.Container_Partitioned WHERE containerId = @ContainerId When I run this, I get a query plan with Actual Partition ...
0
votes
2answers
441 views

How can i update my database from another database using temporary tables?

I want to update tables of my database using another big database every day with using temporary tables and without returning result. How can i update it? This my temporary table : use data_pro; ...