Very slow query, and, and, and...
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.