MySQL Forums
Forum List  »  Performance

Slow join performance
Posted by: Ra Nala
Date: November 27, 2015 04:55AM

Hi Rick James, Based on your input i have raised new thread.

I am facing performance issue while retrieving 4000000 data from database.

nearly i am using 10 tables joins , if we have empty values for retrieving columns then if we update all values to NULL then our query process will be fast, is this correct ?? Please suggest me so that i will update accordingly.

To remove left joins is NOT possible why because i want all records from master table(i.e. prdsl) and i can retrive nulls if the data is not matched from right table.

my query and explain below
-------
select distinct
psrl.id as prdsld,
psrl.psn as psn ,
psrl.bdate as bdate,
psrl.sdate as sdate ,
psrl.idate as idate,
psrl.i_no as i_no ,
psrl.tid as tid,
rn.scode as scode,
rn.rtype as rtype ,
rn.cdate as cdate ,
rn.rdate as rdate ,
rn.rreference as rreference,
rn.rsource as rsource ,
rn.r_industry as r_industry ,
r_application as r_application ,
rn.er_reference as er_reference,
rn.i_no as rn_i_no,
rn.s_per as s_per,
psrl.created_date od_created_date,
psrl.updated_date od_updated_date,
bcu.code as cbe_code,
bcu.type_code as cbe_type_code,
ead.address_1 as c_address_1,
ead.address_2 as c_address_2,
ead.address_3 as c_address_3,
ead.city as c_city,
ead.zip as c_zip,
ead.email as c_email,
rn.p_price as p_price,
rn.p_date as p_date,
be1.code r_be_code,
be1.type_code r_be_type_code,
ea2.address_1 r_be_address_1,
ea2.address_2 r_be_address_2,
ea2.address_3 r_be_address_3,
ea2.city r_be_city,
ea2.email r_be_email,
ea2.zip r_be_zip ,
rn.prdgnd as prdgnd,
psrl.ship_bed as ship_bed,
prd.prdd as modeld,
psrltn.rltd_psn as rltd_psn,
psrltn.rmod as rmod
from prdsl psrl
left join prd prd on psrl.prdd=prd.prdd
left join be_en beship on beship.id=psrl.ship_bed
left join be_en beinv on beinv.id=psrl.invoice_bed
left join be_en reginv on reginv.id=psrl.prdd
left join prdgn rn on rn.prdsld=psrl.id and rn.scode ='abc' and rn.rtype ='xyz'
left join be_en be1 on be1.id = rn.invoice_bed
left join be_gn_ad bea1 on bea1.bed = be1.id and bea1.is_preferred = 'Y'
left join enad ea2 on ea2.id = bea1.be_addressd and ea2.address_type = 'Bil'
left join be_en bcu on bcu.id=rn.cbed
left join enad ead on ead.id= rn.c_addressd
left join prrl psrltn on psrltn.prdsld = psrl.id

id select_type table type possible_keys key key_len ref rows Extra
----- -------------- -------- ------- ------------------- ---------------- ---------- ---------------------------- ------- -----------
1 SIMPLE prdsrl ALL (null) (null) (null) (null) 4000000 (null)
1 SIMPLE prd eq_ref PRIMARY,prod_id_idx PRIMARY 8 prdsrlprod_id 1 (null)
1 SIMPLE regn ref pr_prodsrlid_fk pr_prodsrlid_fk 9 prdsrlid 1 Using where
1 SIMPLE be1 eq_ref PRIMARY PRIMARY 8 regninvoice_be_id 1 (null)
1 SIMPLE bea1 ref bea_beid_fk bea_beid_fk 8 be1id 1 Using where
1 SIMPLE ea2 eq_ref PRIMARY PRIMARY 8 bea1be_address_id 1 Using where
1 SIMPLE becust eq_ref PRIMARY PRIMARY 8 regncust_be_id 1 (null)
1 SIMPLE enaddr eq_ref PRIMARY PRIMARY 8 regncust_address_id 1 (null)
1 SIMPLE psrltn ref fk_prodsrlid_psr fk_prodsrlid_psr 9 prdsrlid 1 (null)

Options: ReplyQuote


Subject
Views
Written By
Posted
Slow join performance
1566
November 27, 2015 04:55AM
914
November 28, 2015 03:02PM
764
February 25, 2016 06: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.