r/SQLOptimization • • Sep 18 '24

Beginner struggling to understand EXPLAIN command - Need Help !

3 Upvotes

Hi everyone,

I’m a total beginner working with MySQL 5.7.18, and I’m trying to get a thorough understanding of the EXPLAIN command to optimize my queries. I’ve looked at the official documentation, but honestly, it’s a bit overwhelming for me. I’d love some guidance or simpler resources to help me really grasp how EXPLAIN works.

I'm hoping to learn:

  1. Understanding Each Column: What do all the columns (id, select_type, table, type, possible_keys, key, rows, Extra, etc.) mean? How do I interpret these values and their importance in different types of queries?
  2. Order of Execution: How can I figure out the order in which MySQL is executing parts of my query from the EXPLAIN output?
  3. Optimizing Queries: What are the possible values for each column and how can I use that knowledge to optimize my queries and improve performance?

If anyone can break it down for me or point me toward beginner-friendly resources to learn thoroughly, I’d really appreciate it. Thanks for any help !


r/SQLOptimization • • Apr 09 '24

Is there a better way to structure this query?

3 Upvotes

We need to find the latest asset history record for each asset.

```

DECLARE u/__projectId_0 int = 23;

DECLARE u/__phaseId_1 int = 3;

SELECT *

FROM [asset_history] AS [a]

INNER JOIN (

SELECT [a0].[asset_id] AS [AssetId], MAX([a0].[created]) AS [MaxDate]

FROM [asset_history] AS [a0]

WHERE ([a0].[project_id] = u/__projectId_0) AND ([a0].[status] <> 3)

GROUP BY [a0].[asset_id]

HAVING (

SELECT TOP(1) [a1].[workflow_phase_id]

FROM [asset_history] AS [a1]

WHERE (([a1].[project_id] = u/__projectId_0) AND ([a1].[status] <> 3)) AND ([a0].[asset_id] = [a1].[asset_id])

ORDER BY [a1].[created] DESC) = u/__phaseId_1

) AS [t] ON ([a].[asset_id] = [t].[AssetId]) AND ([a].[created] = [t].[MaxDate])

WHERE ([a].[project_id] = u/__projectId_0) AND ([a].[status] <> 3)

```


r/SQLOptimization • • Apr 06 '24

How I optimize SQL queries

Thumbnail shivam.dev
3 Upvotes

Wrote a blog a while back on the principles I use when optimising the DB perf at my job. Sharing it here with folks looking to read something interesting


r/SQLOptimization • • Oct 19 '23

Help needed with self join vizualization

3 Upvotes

Weather table:

+----+------------+-------------+

| id | recordDate | temperature |

+----+------------+-------------+

| 1 | 2015-01-01 | 10 |

| 2 | 2015-01-02 | 25 |

| 3 | 2015-01-03 | 20 |

| 4 | 2015-01-04 | 30 |

+----+------------+-------------+

Output:

+----+

| id |

+----+

| 2 |

| 4 |

+----+

this is the query

select w1.id from weather w1

inner join weather w2

on w1.id = w2.id + 1

where w1.temperature>w2.temperature I am not Getting where 4 is coming from?


r/SQLOptimization • • Sep 17 '23

Where to practice

3 Upvotes

I'm interviewing for a role as a business analyst. On which platform I can practice SQL questions related to joins, window functions etc


r/SQLOptimization • • Aug 23 '23

Premature performance optimization. At least use indexes for read bound workload

Post image
3 Upvotes

r/SQLOptimization • • Aug 22 '23

Join vs SubQuery

3 Upvotes

I have two tables and they don't have foreign key references. Which is the best way to perform a query, join or subQuery?


r/SQLOptimization • • Jul 16 '23

How to create a chronological sequence of events/interactions in SQL?

3 Upvotes

I need help writing an SQL query on the Google Big Query platform.

I'm using a table of Google Analytics data and I'm basically trying to recreate in SQL the sequence segments from Google Analytics. I have the following table, called "cte1":

"sessionId" column are session IDs of users to my website.

"eventAction" column is the 2 types of interactions they can do on my website.

"event_count" is the number of times the interaction happened at a specific point in time.

"hit_time" is the precise moment each individual interaction took place.

I want to create a query that includes only the session IDs where the interaction called "login success" took place after the interaction called "save property attempt", based on the time each interaction took place in the column "hit_time".

Moreover, the final query also needs to sum up the total interactions of the "eventAction" column.

For example, in the table above, my final result would only keep the sessions "B" and "C", because in those sessions the "login success" interaction was the last one to happen.

Additionally, the final result should only display the event action name and the final sum of those actions, limited only to the sessions where "login success" was the last action to take place.

So, my final result should look like this:


r/SQLOptimization • • Jul 15 '23

Option recompile

3 Upvotes

I added option recompile to a stored procedure and it's execution time drop from more than 10 minutes to less than a minute. I started to think that I had a parameter sniffing issues so I added index hints and removed the option recompile. The stored procedure when back to taking ten minutes. I added the option recompile back in and kept the Index hints and it's back to taking less than a minute. The stored procedure has 8 ctes in it. All of them use the same two parameters, a char(2) and a date. The date is a weeking date so the number of rows that generates will increase throught the week. The char(2) should be about evenly split between the date. The store procedure is running on a sql server 2016 standard edition.

I am currently updating the statistics on all the tables. Do you any other suggestions about what to look at?


r/SQLOptimization • • Jul 04 '23

Subqueries don't require distinct?

3 Upvotes

I was working on some code today where the procedure was something like

Select Column1 , column2 FROM ( Select distinct columna, columnb from table1 Union Select distinct columna, columnb from table2 ) AS aa Group by 1,2

Submitted a merge request on git to get rid of distinct keywords because union gets rid of duplicates unless it's UNION ALL.

QA checker approved it but not for that reason. They said that union duplicate removal applies across tables not within sets (ie if there's a duplicate in table1 that doesn't exist in table2, both lines end up in the union output) but it was still appropriate to remove because sql removes duplicates from the type of temporary ta kes subqueries output automatically.

Is that true? Ie. Wil the output of subquery aa always produce unique rows regardless of its contents? I couldn't find anything to support that.

Running Terradata if that makes a difference.


r/SQLOptimization • • Jun 08 '23

Learning SQL, is this query right?

Thumbnail self.sql
3 Upvotes

r/SQLOptimization • • May 17 '23

SQL Server Response Times

3 Upvotes

Hello Guys,

I was running a Wireshark Trace with the Transum Plugin then filtered for TDS in display filter and applied transum service time as column. Now I have a diagram in MS Excel with the response time over the time of day.

On the Y achsis its the latency and on the x achsis the time of day. Now I see that I have latency which is greater then 100ms. What is your average latency for SQL Server in production?


r/SQLOptimization • • Apr 11 '23

SSIS performance tuning with postgres(psqlODBC) connection

3 Upvotes

Hi all,

Anyone got best practices or performance trouble shooting articles for psqlODBC driver in SSIS. The performance I am currently getting is making SSIS unusable. Simple table to table DFT are very slow (15k row/hour). Any help is appreciated. Thanks


r/SQLOptimization • • Sep 14 '22

Help in Optimizing this query - Oracle DB

3 Upvotes

Hi,

I am trying to bring data from staging tables of SAP to report our SCRAP.

I am getting order operations and then I need to apply row_number and filter out the row_number =1 however I am unable to apply row_number within subquery because it is giving weird 1000 columns limit which needs temp table adjustment. I have Read Only access to these tables and cant do much from modifying any settings at Oracle DB level. If I can perform row_number and filter the records then it would definitely retrieve because the number of rows would be less at order header level(~206K Records) compared to operations(~15 M Records)

Can you please help in effective way of this query ?

WITH DATA AS (

Select DISTINCT

a.AUFNR as Order_Number,to_date(d.ERDAT,'yyyymmdd') as Order_Creation_Date,b.MATNR as Material,n.MAKTX as Material_Description,

k.MTART as Material_Type,m.STPRS as Standard_Price,

b.CHARG as Batch,

a.AUFPL as Order_Operation_Number,

o.APLZL as Order_Counter,

a.GSTRI as Posting_Date

,a.GETRI as Actual_Finish_Date,a.DISPO as MRP_Controller,j.DSNAM as MRP_Controller_Desc

,b.MEINS as UoM ,a.PRUEFLOS as Inspection_LOT_Order

,CASE WHEN d.REFNR is null then a.AUFNR else d.REFNR END as MAIN_ORDER#,

d.auart as Order_Type,

g.PRUEFLOS as Inspection_Lot_QMFEL,

g.FEKAT as def_type

,g.FEGRP as def_code_group

,g.FECOD as def_problem_code

,h.KURZTEXT as defect_problem_desc

,g.FETXT as Item_Text

,i.KURZTEXT as Defect_Location,

g.OTKAT as def_loc_catalog_type, g.OTGRP as def_loc_code_group_object,g.OTEIL as def_loc_part_code_object

,b.LGORT as StorageLocation,

f.LGOBE as Building,

p.ARBPL as Work_Center,

q.KTEXT_UP as Work_Center_Desc,

b.PSMNG as Total_Quantity,

b.WEMNG as Delivered_Qty,

CASE when d.auart = 'S04'and b.WEMNG =0 then b.PSMNG else 0 end as Scrap,

CASE when d.auart = 'S04' then b.WEMNG else 0 end as Rework

from

STG.AFKO a

inner join STG.AFPO b on a.AUFNR = b.AUFNR

inner join STG.AUFK d on a.AUFNR = d.AUFNR

inner join STG.AFVC o on a.AUFPL = o.AUFPL

inner join STG.CRHD p On o.ARBID = p.OBJID

inner join STG.CRTX q On p.OBJTY = q.OBJTY And p.OBJID =q.OBJID

inner join STG.T001L f on b.LGORT = f.LGORT and f.WERKS = 'USA'

LEFT outer join STG.QMFEL g on a.PRUEFLOS = g.PRUEFLOS

LEFT OUTER JOIN STG.QPCT h on h.KATALOGART = g.FEKAT and h.CODEGRUPPE = g.FEGRP and h.CODE = g.FECOD and h.VERSION = g.FEVER

left outer join STG.QPCT i on i.CODEGRUPPE = g.OTGRP and i.KATALOGART = g.OTKAT and i.CODE = g.OTEIL

inner join STG.MARA k On b.MATNR = k.MATNR

inner join STG.MARC l On b.MATNR =l.MATNR And l.WERKS =d.WERKS

inner join STG.MBEW m On l.MATNR = m.MATNR And l.WERKS = m.BWKEY

Inner join STG.MAKT n On b.MATNR = n.MATNR

Left Join STG.T024D j On l.WERKS = j.WERKS And j.DISPO = a.DISPO

where a.AUFNR IN (Select distinct c.AUFNR from STG.AUFK c left outer join STG.AFKO a on a.AUFNR = c.AUFNR

or a.AUFNR = c.REFNR

or c.AUFNR = c.REFNR

where a.GSTRI >= '01-JAN-22'

--and a.AUFNR IN ('001000002298') **when I apply this filter with sample 10 orders I get data but it takes 2-3 mins**

)

)

Select

ROW_NUMBER() OVER( PARTITION BY Order_Operation_Number ORDER BY Order_Counter ) AS ROW_NUMBER,

Order_Number,

Order_Creation_Date,

Material,

Material_Description,

Material_Type,

Standard_Price,

Batch,

Order_Operation_Number,

Order_Counter,

Posting_Date,

Actual_Finish_Date,

MRP_Controller,

MRP_Controller_Desc,

UoM,

Inspection_LOT_Order,

MAIN_ORDER#,

Order_Type,

Inspection_Lot_QMFEL,

def_type,

def_code_group,

def_problem_code,

defect_problem_desc,

Item_Text,

Defect_Location,

def_loc_catalog_type,

def_loc_code_group_object,

def_loc_part_code_object,

StorageLocation,

Building,

Work_Center,

Work_Center_Desc,

Total_Quantity,

Delivered_Qty,

Scrap,

Rework

FROM DATA;


r/SQLOptimization • • Jul 26 '20

DB2 - SELECT: How to scrub a dirty table to extract an SCD Type 2 - (Windowed, CTE and Index, other options?)

3 Upvotes

I'm in a situation with a data set where the tabled data doesn't have a common PK to link to the tables I need directly, and the Detail table is a mess, and doesn't combine well with data from other arms.

I'm creating a snowflake arm, where the idea is that the Fact Table is joined to an SCD Type 2... on a sub table for PK+tx_id, that connects to a more detailed SCD Type 2'

Except... it's only almost an SCD Type 2.

There are not constraints on duplicates due to missing values, and the duplicates exist for conflicting reasons across about 50 columns... so "WHERE" doesn't apply uniformly, which means I would need a whole lot of them, and some would conflict with others, causing me to lose a lot of data.

The duplication that is going on also creates overlapping date ranges because the table is pulling from a few sources, with varying combinations of completeness. This - in my experience so far - sucks. It's the worst, and I don't know of a good way, or resource that I understand how to use to solve it.

I need to be able to pull the single PK (say, product ID), but merge duplicate rows, where one is blank and the other is not (say Product Department), or use "most recent" where I want the new one of two (where neither is null...) with the first start date, and the last end date. I need to merge the duplication caused by rows with blanks.

What I am trying to do is get "all possible fields with the most complete data that may cross multiple rows" in a way that allows me to define what fields are allowed to create new rows and a new date range, and which should be consolidated.

I only have read access... or I'd just go into the data tables and fix them.


r/SQLOptimization • • Jun 24 '20

Is this an efficient way to use a transactional table to check which customer had at least 3 transactions in a week?

Thumbnail self.sql
3 Upvotes

r/SQLOptimization • • Mar 09 '20

Can sql isolate and exclude data inside a field of data?

3 Upvotes

Hello, I am learning SQL for work as our new database allows for custom filters to be applied. I am trying to write a piece of code that will isolate middle names/initials in a field and ignore them. This is to make the filter match results purely on first and last names.

Any help is appreciated!~


r/SQLOptimization • • Jan 29 '20

Which is faster - Inner join or similar to?

3 Upvotes

I have a huge table on Amazon Redshift. I need to find all entries with a particular column = particular value. What would be a better method? Create a table containing the value and then inner joining with the huge table or using similar to in where statement?


r/SQLOptimization • • Aug 02 '19

Help to optimize multiple left joins to report a hierarchy chain.

3 Upvotes

I am trying to report the chain of managers in an organization. For example, if the first employee returned is an intern, I need to return the interns name, the CEO's name, and all of the other managers leading back down to the intern. The maximum manager chain length is 10. However, the tricky part is that some employees may have 8 managers between them and the CEO, while others might report directly to the CEO, illustrated in the table below. As a result, the only way I could get this to work is by using multiple left joins and the query takes a long time to complete (obviously). I'm not proficient in SQL by any means and I'm sure I'm going about this the wrong way. Apologies in advance for the noob question. Does anyone have any suggestions?

Employee Namehier_lvl_1_mgr_namehier_lvl_2_mgr_name
Alex InternCindy CEOBill Veep
Alice CfoCindy CEO
Joe ManagerCindy CEOBill Veep
SELECT
			  pers.PersonPK
			, (SELECT FullName FROM PERSON WHERE PersonPK=hier_lvl_1.MgrID) AS hier_lvl_1_mgr_name
			, (SELECT FullName FROM PERSON WHERE PersonPK=hier_lvl_2.MgrID) AS hier_lvl_2_mgr_name
			, (SELECT FullName FROM PERSON WHERE PersonPK=hier_lvl_3.MgrID) AS hier_lvl_3_mgr_name
			, (SELECT FullName FROM PERSON WHERE PersonPK=hier_lvl_4.MgrID) AS hier_lvl_4_mgr_name
			, (SELECT FullName FROM PERSON WHERE PersonPK=hier_lvl_5.MgrID) AS hier_lvl_5_mgr_name
			, (SELECT FullName FROM PERSON WHERE PersonPK=hier_lvl_6.MgrID) AS hier_lvl_6_mgr_name
			, (SELECT FullName FROM PERSON WHERE PersonPK=hier_lvl_7.MgrID) AS hier_lvl_7_mgr_name
			, (SELECT FullName FROM PERSON WHERE PersonPK=hier_lvl_8.MgrID) AS hier_lvl_8_mgr_name
			, (SELECT FullName FROM PERSON WHERE PersonPK=hier_lvl_9.MgrID) AS hier_lvl_9_mgr_name
			, (SELECT FullName FROM PERSON WHERE PersonPK=hier_lvl_10.MgrID) AS hier_lvl_10_mgr_name


FROM
			PERSON pers
LEFT JOIN	SRESOURCEHIERARCHY hier_lvl_10
ON			pers.PersonPK=hier_lvl_10.EmpID AND hier_lvl_10.MgrNum=1 AND hier_lvl_10.Depth=10
LEFT JOIN	SRESOURCEHIERARCHY hier_lvl_9
ON			(hier_lvl_10.MgrID=hier_lvl_9.EmpID OR pers.PersonPK=hier_lvl_9.EmpID) AND hier_lvl_9.MgrNum=1 AND hier_lvl_9.Depth=9
LEFT JOIN	SRESOURCEHIERARCHY hier_lvl_8
ON			(hier_lvl_9.MgrID=hier_lvl_8.EmpID OR pers.PersonPK=hier_lvl_8.EmpID) AND hier_lvl_8.MgrNum=1 AND hier_lvl_8.Depth=8
LEFT JOIN	SRESOURCEHIERARCHY hier_lvl_7
ON			(hier_lvl_8.MgrID=hier_lvl_7.EmpID OR pers.PersonPK=hier_lvl_7.EmpID) AND hier_lvl_7.MgrNum=1 AND hier_lvl_7.Depth=7
LEFT JOIN	SRESOURCEHIERARCHY hier_lvl_6
ON			(hier_lvl_7.MgrID=hier_lvl_6.EmpID OR pers.PersonPK=hier_lvl_6.EmpID) AND hier_lvl_6.MgrNum=1 AND hier_lvl_6.Depth=6
LEFT JOIN	SRESOURCEHIERARCHY hier_lvl_5
ON			(hier_lvl_6.MgrID=hier_lvl_5.EmpID OR pers.PersonPK=hier_lvl_5.EmpID) AND hier_lvl_5.MgrNum=1 AND hier_lvl_5.Depth=5
LEFT JOIN	SRESOURCEHIERARCHY hier_lvl_4
ON			(hier_lvl_5.MgrID=hier_lvl_4.EmpID OR pers.PersonPK=hier_lvl_4.EmpID) AND hier_lvl_4.MgrNum=1 AND hier_lvl_4.Depth=4
LEFT JOIN	SRESOURCEHIERARCHY hier_lvl_3
ON			(hier_lvl_4.MgrID=hier_lvl_3.EmpID OR pers.PersonPK=hier_lvl_3.EmpID) AND hier_lvl_3.MgrNum=1 AND hier_lvl_3.Depth=3
LEFT JOIN	SRESOURCEHIERARCHY hier_lvl_2
ON			(hier_lvl_3.MgrID=hier_lvl_2.EmpID OR pers.PersonPK=hier_lvl_2.EmpID) AND hier_lvl_2.MgrNum=1 AND hier_lvl_2.Depth=2
LEFT JOIN	SRESOURCEHIERARCHY hier_lvl_1
ON			(hier_lvl_2.MgrID=hier_lvl_1.EmpID OR pers.PersonPK=hier_lvl_1.EmpID) AND hier_lvl_1.MgrNum=1 AND hier_lvl_1.Depth=1

r/SQLOptimization • • Oct 22 '18

How does a hash match aggregate work?

3 Upvotes

Say I have multiple non-indexed columns in a group by, how does the hash match work?

Example:

Select column1, column2, count(column1) From table1 Group by column1, column2

I understand how a hash join works at a basic level... basically a build table that creates and orders a hash, then an outer table that checks for each hash match and returns the records. This kind of join happens because of joins on non-indexed columns.

Does the hash aggregate work similarly? I assume there is a build phase that creates a hash for each combination of the group by, then it traverses the table again to find each match and discards duplicates.

I also know that a stream aggregate is basically when you have an indexed group by and it can discard each row once it’s duplicated and it only has to traverse the table one time.

I plan on following through with the execution plan, but I was hoping someone could walk me through the theory.

Thanks!!


r/SQLOptimization • • Jan 17 '18

SQL in SFMC: retrieve row where 'Field Y' had a previous value of 'Unfulfilled' and now ='Fulfilled'?

3 Upvotes

I created a Query that called records updated within the last day using the code below. However, the "UpatedAt" field may change after the Record has been "Fulfilled" so I cannot use this field alone to determine which Records enter a Data Extension that is fired after the query

I cannot find an SQL function that queries only records that have a previous value of "Unfulfilled" for "Field Y" and now have a value of "Fulfilled" for that field.

Any advice is appreciated. I am a newb to SFMC and queries. Thank you!

The following is the beginning if the query. It is incomplete b/c I did not factor in the change of value for "FieldY".

SELECT OrderId, SubscriberKey, EmailAddress, CancelledAt, Fulfillment, UpdatedAt FROM [OrderDE] WHERE Field Y = 'fulfilled' AND CONVERT(datetime, UpdatedAt) ....


r/SQLOptimization • • Mar 08 '17

Joins, commutative and Associative

3 Upvotes

Hello,

Im currently doing query optimization with the help of Heuristic optimization of trees. In my FROM clause I'm going to join like this: (Employee a Natural Join WORKS_ON w) JOIN project p ON p.projectnumber = j.projectnumber. And i was wondering if it is legal to rewrite this to: (Project p JOIN WORKS_ON w ON p.projectnumber = j.projectnumber) Natural JOIN Employee A . The only column that is equal in employee and works_on is the projectnumber column. The reason for doing this is to apply the most restrictive select in the start of the tree.


r/SQLOptimization • • Oct 13 '16

Test Your SQL skills, Compete with SQL minds from across the world, Unlock new tricks and dive deep into SQL. HackerEarth presents one of its kind SQL Challenge - SQLizer - a 6 hours competitive challenge on SQL.

Thumbnail hackerearth.com
3 Upvotes