MySQL Forums
Forum List  »  MySQL Workbench

MySql using 60+ GB of memory to run one query
Posted by: Carrie Fearer
Date: May 19, 2023 11:49AM

Hello all. I am new to MySQL (previously used SQlite3) and am trying to run the below query on my mac (version 12.6), but it is using over 60GB of data to run and I am running out of disk space. I am running MySQL version 8.0.3. I have tried optimizing parameters in the options field but have had no luck. Any assistance related to this issue would be greatly appreciated. Thank you!

SELECT num.GRP1,
num.GRP2,
num.ESTIMATE/den.ESTIMATE RATIO_ESTIMATED_VALUE,
num.ESTIMATE NUM_ESTIMATED_VALUE,
den.ESTIMATE DEN_ESTIMATED_VALUE
FROM (SELECT GRP1,
GRP2,
sum(ESTIMATED_VALUE * EXPNS) ESTIMATE
FROM (SELECT pop_stratum.estn_unit_cn,
pop_stratum.cn STRATACN,
case coalesce(cond.alstkcd,-1)
when 1 then
'`0001 Overstocked'
when 2 then
'`0002 Fully stocked'
when 3 then
'`0003 Medium stocked'
when 4 then
'`0004 Poorly stocked'
when 5 then
'`0005 Nonstocked'
when -1 then
'`0006 Unavailable'
else
'`0007 Other'
end GRP1,
case coalesce(cond.alstkcd,-1)
when 1
then
'`0001 Overstocked'
when 2
then '`0002 Fully stocked'
when 3 then
'`0003 Medium stocked'
when 4 then
'`0004 Poorly stocked'
when 5 then
'`0005 Nonstocked'
when -1 then
'`0006 Unavailable'
else '`0007 Other'
end GRP2,
plot.cn,
plot.prev_plt_cn,
plot.lat,
plot.lon,
pop_stratum.expns EXPNS,
SUM(GRM.TPAGROW_UNADJ *
(CASE WHEN COALESCE(GRM.SUBPTYP_GRM,0) = 0
THEN (0)
WHEN GRM.SUBPTYP_GRM = 1
THEN POP_STRATUM.ADJ_FACTOR_SUBP
WHEN GRM.SUBPTYP_GRM = 2
THEN POP_STRATUM.ADJ_FACTOR_MICR
WHEN GRM.SUBPTYP_GRM = 3
THEN POP_STRATUM.ADJ_FACTOR_MACR ELSE (0)
END) *
(CASE WHEN BE.ONEORTWO = 2
THEN (CASE WHEN (GRM.COMPONENT = 'SURVIVOR'
OR GRM.COMPONENT = 'INGROWTH'
OR GRM.COMPONENT LIKE 'REVERSION%')
THEN (TREE.DRYBIO_AG/2000/PLOT.REMPER)
WHEN (GRM.COMPONENT LIKE 'CUT%'
OR GRM.COMPONENT LIKE 'DIVERSION%') THEN
(TRE_MIDPT.DRYBIO_AG/2000/PLOT.REMPER)
ELSE (0)
END)
ELSE
(CASE WHEN (GRM.COMPONENT = 'SURVIVOR'
OR GRM.COMPONENT = 'CUT1'
OR GRM.COMPONENT = 'DIVERSION1'
OR GRM.COMPONENT = 'MORTALITY1')
THEN CASE WHEN TRE_BEGIN.TRE_CN IS NOT NULL
THEN -(TRE_BEGIN.DRYBIO_AG/2000/PLOT.REMPER)
ELSE
-(PTREE.DRYBIO_AG/2000/PLOT.REMPER)
END
ELSE (0)
END)
END)) AS ESTIMATED_VALUE
FROM BEGINEND BE,
POP_STRATUM POP_STRATUM
JOIN POP_PLOT_STRATUM_ASSGN POP_PLOT_STRATUM_ASSGN
ON (POP_STRATUM.CN = POP_PLOT_STRATUM_ASSGN.STRATUM_CN)
JOIN PLOT PLOT
ON (POP_PLOT_STRATUM_ASSGN.PLT_CN = PLOT.CN)
JOIN PLOTGEOM PLOTGEOM
ON (PLOT.CN = PLOTGEOM.CN)
JOIN PLOT PPLOT
ON (PLOT.PREV_PLT_CN = PPLOT.CN)
JOIN COND PCOND
ON (PLOT.PREV_PLT_CN = PCOND.PLT_CN)
JOIN COND COND
ON (PLOT.CN = COND.PLT_CN)
JOIN TREE TREE
ON (TREE.CONDID = COND.CONDID
AND TREE.PLT_CN = PLOT.CN
AND TREE.PREVCOND = PCOND.CONDID)
LEFT OUTER JOIN TREE PTREE
ON (TREE.PREV_TRE_CN = PTREE.CN)
LEFT OUTER JOIN TREE_GRM_BEGIN TRE_BEGIN
ON (TREE.CN = TRE_BEGIN.TRE_CN)
LEFT OUTER JOIN TREE_GRM_MIDPT TRE_MIDPT
ON (TREE.CN = TRE_MIDPT.TRE_CN)
LEFT OUTER JOIN (SELECT TRE_CN,
DIA_BEGIN,
DIA_MIDPT,
DIA_END,
MICR_COMPONENT_AL_FOREST AS COMPONENT,
MICR_SUBPTYP_GRM_AL_FOREST AS SUBPTYP_GRM,
MICR_TPAGROW_UNADJ_AL_FOREST AS TPAGROW_UNADJ
FROM TREE_GRM_COMPONENT)
GRM ON (TREE.CN = GRM.TRE_CN)
WHERE 1=1
AND ((pop_stratum.rscd=24 and pop_stratum.evalid=231503))
and (1=1 and tree.spcd = 531)
and (1=1)
GROUP BY pop_stratum.estn_unit_cn,
pop_stratum.cn,
plot.cn,
plot.prev_plt_cn,
plot.lat,
plot.lon,
pop_stratum.expns,
case coalesce(cond.alstkcd,-1)
when 1 then
'`0001 Overstocked'
when 2 then
'`0002 Fully stocked'
when 3 then
'`0003 Medium stocked'
when 4 then
'`0004 Poorly stocked'
when 5 then
'`0005 Nonstocked'
when -1 then
'`0006 Unavailable'
else '`0007 Other'
end,
case coalesce(cond.alstkcd,-1)
when 1 then
'`0001 Overstocked'
when 2 then
'`0002 Fully stocked'
when 3 then
'`0003 Medium stocked'
when 4 then
'`0004 Poorly stocked'
when 5 then
'`0005 Nonstocked'
when -1 then
'`0006 Unavailable'
else
'`0007 Other'
end) sub
GROUP BY GRP1,
GRP2
ORDER BY GRP1,
GRP2) num
join (SELECT GRP1,
GRP2,
sum(ESTIMATED_VALUE * EXPNS)
ESTIMATE FROM (SELECT pop_stratum.estn_unit_cn,
pop_stratum.cn STRATACN,
case coalesce(cond.alstkcd,-1)
when 1 then
'`0001 Overstocked'
when 2 then
'`0002 Fully stocked'
when 3 then
'`0003 Medium stocked'
when 4 then
'`0004 Poorly stocked'
when 5 then
'`0005 Nonstocked'
when -1 then
'`0006 Unavailable'
else
'`0007 Other'
end GRP1,
case coalesce(cond.alstkcd,-1)
when 1 then
'`0001 Overstocked'
when 2 then
'`0002 Fully stocked'
when 3 then
'`0003 Medium stocked'
when 4 then
'`0004 Poorly stocked'
when 5 then
'`0005 Nonstocked'
when -1
then '`0006 Unavailable'
else
'`0007 Other'
end GRP2,
plot.cn,
plot.prev_plt_cn,
plot.lat,
plot.lon,
pop_stratum.expns EXPNS,
SUM(TREE.TPA_UNADJ *
CASE WHEN TREE.DIA IS NULL
THEN
POP_STRATUM.ADJ_FACTOR_SUBP
ELSE
CASE LEAST(TREE.DIA, 5 - 0.001)
WHEN TREE.DIA
THEN
POP_STRATUM.ADJ_FACTOR_MICR
ELSE
CASE LEAST(TREE.DIA, COALESCE(PLOT.MACRO_BREAKPOINT_DIA, 9999) - 0.001)
WHEN TREE.DIA
THEN
POP_STRATUM.ADJ_FACTOR_SUBP
ELSE
POP_STRATUM.ADJ_FACTOR_MACR
END
END
END * COALESCE(TREE.DRYBIO_AG / 2000, 0)) AS ESTIMATED_VALUE
FROM POP_STRATUM POP_STRATUM
JOIN POP_PLOT_STRATUM_ASSGN
ON (POP_PLOT_STRATUM_ASSGN.STRATUM_CN = POP_STRATUM.CN)
JOIN PLOT
ON (POP_PLOT_STRATUM_ASSGN.PLT_CN = PLOT.CN)
JOIN PLOTGEOM
ON (PLOT.CN = PLOTGEOM.CN)
JOIN COND
ON (COND.PLT_CN = PLOT.CN)
JOIN TREE
ON (TREE.PLT_CN = COND.PLT_CN
AND TREE.CONDID = COND.CONDID)
left outer join TREE PTREE
on (TREE.PREV_TRE_CN = PTREE.CN)
left outer join cond pcond
on (pcond.plt_cn=ptree.plt_cn
and pcond.condid=ptree.condid)
where 1=1
and TREE.STATUSCD = 1
AND COND.COND_STATUS_CD = 1
AND ((pop_stratum.rscd=24
and pop_stratum.evalid=231503))
and (tree.spcd = 531)
and (1=1)
GROUP BY pop_stratum.estn_unit_cn,
pop_stratum.cn,
plot.cn,
plot.prev_plt_cn,
plot.lat,
plot.lon,
pop_stratum.expns,
case coalesce(cond.alstkcd,-1)
when 1 then
'`0001 Overstocked'
when 2 then
'`0002 Fully stocked'
when 3 then
'`0003 Medium stocked'
when 4 then
'`0004 Poorly stocked'
when 5 then
'`0005 Nonstocked'
when -1 then '`0006 Unavailable'
else
'`0007 Other'
end,
case coalesce(cond.alstkcd,-1)
when 1 then
'`0001 Overstocked'
when 2 then
'`0002 Fully stocked'
when 3 then
'`0003 Medium stocked'
when 4 then
'`0004 Poorly stocked'
when 5 then
'`0005 Nonstocked'
when -1 then
'`0006 Unavailable'
else
'`0007 Other'
end) sub2
GROUP BY GRP1,
GRP2
ORDER BY GRP1,
GRP2) den
ON num.GRP1=den.GRP1
AND num.GRP2=den.GRP2;

Options: ReplyQuote


Subject
Views
Written By
Posted
MySql using 60+ GB of memory to run one query
215
May 19, 2023 11:49AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.