MySQL Forums
Forum List  »  Newbie

Very slow query, and, and, and...
Posted by: Gerrit Andresen
Date: January 05, 2015 08:22AM

Hello everyone,

i just make my first bigger experience with mysql and now i stuck on a problem. I have tried various things but i dont know how i can insert joins or make the query a bit lighter. So i created a query with severals "and(s)" and now my execution time grows up to nearby 3 seconds... horrible (like my query).

I have a little db with several tables in it which are connected over n:m tables (normalization). I have a table called tags which is nearly connected to any other, and i have a table called group_matches where all n_2_tags:n are grouped.


Tablestructure:
---------------
customers : cu_id, cu_code
custcomb : cc_id, sl_id, lc_id (sl_id, lc_id refers to customers.cu_id)
custcomb_2_tags: cctg_id, cc_id, tg_id
tags : tg_id, tg_code
components: co_id, co_code
components_2_tags: cotg_id, co_id, tg_id
dimensions: dm_id, dm_x, dm_y
dimensions_2_tags: dmtg_id, dm_id, tg_id
materials : ma_id, ma_code
materials_2_tags: matg_id, ma_id, tg_id
locations : lo_id, lo_code
locations_2_tags: lotg_id, lo_id, tg_id
isocodes : ic_id, ic_code
isocodes_2_tags: ictg_id, ic_id, tg_id
group_materialrates: gmr_id, cctg_id, ictg_id, lotg_id, cotg_id, matg_id, dmtg_id, tg_id
select_materialrates: smr_id, gmr_id, mr_id
materialrates: mr_id, price_usd


---- QUERY ----

SELECT * FROM

customers AS cu1,
customers AS cu2,
custcomb AS cc,
custcomb_2_tags AS cc2tg,
tags AS tgcc,
components AS co,
components_2_tags AS co2tg,
tags AS tgco,
dimensions AS dm,
dimensions_2_tags AS dm2tg,
tags AS tgdm,
materials AS ma,
materials_2_tags AS ma2tg,
tags AS tgma,
locations AS lo,
locations_2_tags AS lo2tg,
tags AS tglo,
isocodes AS ic,
isocodes_2_tags AS ic2tg,
tags AS tgic,
group_materialrates AS gmr,
select_materialrates AS smr,
materialrates AS mr


WHERE

cu1.cu_code = "COMPANY1"
AND
cu2.cu_code = "COMPANY2"
AND
co.co_code = "PATCH"
AND
dm.dm_x = "500"
AND
dm.dm_y = "500"
AND
ma.ma_code = "STEEL"
AND
lo.lo_code = "TOP"
AND
ic.ic_code = "PCS55"
AND

tgcc.tg_code = "NONE"
AND
tgma.tg_code = "NONE"
AND
tglo.tg_code = "NONE"
AND
tgco.tg_code = "NONE"
AND
tgdm.tg_code = "NONE"
AND
tgic.tg_code = "NONE"
AND
tggmr.tg_code = "NONE"
AND


cc.sl_id = sl.cu_id
AND
cc.lc_id = lc.cu_id
AND

cctg.cc_id = cc.cc_id
AND
cctg.tg_id = tgcc.tg_id
AND

ictg.ic_id = ic.ic_id
AND
ictg.tg_id = tgic.tg_id
AND

cotg.co_id = co.co_id
AND
cotg.tg_id = tgco.tg_id
AND

dmtg.dm_id = dm.dm_id
AND
dmtg.tg_id = tgdm.tg_id
AND

matg.ma_id = ma.ma_id
AND
matg.tg_id = tgma.tg_id
AND

lotg.lo_id = lo.lo_id
AND
lotg.tg_id = tglo.tg_id
AND

gmr.cctg_id = cctg.cctg_id
AND
gmr.ictg_id = ictg.ictg_id
AND
gmr.lotg_id = lotg.lotg_id
AND
gmr.cotg_id = cotg.cotg_id
AND
gmr.matg_id = matg.matg_id
AND
gmr.dmtg_id = dmtg.dmtg_id
AND
gmr.tg_id = tggmr.tg_id
AND

smr.gmr_id = gmr.gmr_id
AND
smr.mr_id = mr.mr_id


--------------

Hopefully you can help me on this. I have 2 other query which contains nearly the same amount of "ands" but it needs only around 0.02 - 0.06 seconds.

Thanks.

Options: ReplyQuote


Subject
Written By
Posted
Very slow query, and, and, and...
January 05, 2015 08:22AM


Sorry, you can't reply to this topic. It has been closed.

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.