Slow join performance

[Ra Nala]

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)