Slow in fetching records from table using left join

[ipskalsi]

I have a table Multiple user at same time perform multiple operation on that table like insert , update , fetch etc. During fetching records i use many left outer join which decrease the performance. Is there is any solution to fetch the records fast. or i have to change the structue of table using normilisation.

any alternate of join.?

this is final query that executed..

select `customer`.`customerid` AS `customerid`,`customer`.`customername` AS `customername`,`customer`.`customeraddress1` AS `address1`,
       `customer`.`customeraddress2` AS `address2`,`customer`.`customeraddress3` AS `address3`,`customer`.`customeraddress4` AS `address4`,
       `customer`.`customerincomingno` AS `contact1`,`customer`.`customerphone2` AS `contact2`,`customer`.`customerphone3` AS `contact3`,
       `customer`.`customeremailid` AS `emailid`,`customer`.`customerphone4` AS `contact4`,`customer`.`entrydate` AS `entrydate`,
       `area`.`area` AS `area`,`pincode`.`pincode` AS `pincode`,`city`.`city` AS `city`,`city`.`stdcode` AS `stdcode`,
       `tehsil`.`tehsilname` AS `tehsilname`,`district`.`districtname` AS `districtname`,`state`.`statename` AS `statename`,
       `country`.`countryname` AS `countryname`,`customer`.`calldate` AS `calldate`,`order`.`advance` AS `advance`,
       `order`.`paymentdate` AS `paymentdate`,`order`.`returndate` AS `returndate`,`order`.`dispatcherremark` AS `dispatcherremark`,
       `order`.`remark` AS `remark`,`order`.`quantity` AS `quantity`,`order`.`price` AS `price`,`order`.`pnhcharges` AS `pnhcharges`,
       `order`.`tax` AS `tax`,`order`.`discount` AS `discount`,`order`.`total` AS `total`,`order`.`tilldate` AS `tilldate`,
       `order`.`statusdate` AS `statusdate`,`order`.`onlinestatus` AS `onlinestatus`,`callhour`.`displaycallhour` AS `displaycallhour`,
       `channel`.`channelname` AS `channelname`,`order`.`orderid` AS `orderid`,`order`.`orderdate` AS `orderdate`,`order`.`avdate` AS `avdate`,
       `order`.`ocdate` AS `ocdate`,`order`.`bcdate` AS `bcdate`,`order`.`docketno` AS `docketno`,`paymentmode`.`paymentmode` AS `paymentmode`,
       `extension`.`extensionname` AS `extensionname`,`callcenter`.`callcentername` AS `callcentername`,`orderdetail`.`avquantity` AS `avquantity`,
       `orderdetail`.`ocquantity` AS `ocquantity`,`product`.`productname` AS `productname`,       
       case fkdispatchtypeid when 3 then 'Branch' when 5 then 'Courier' when 6 then 'Franchisee' end as `Dispatch By`,
       case fkdispatcherid when `courier`.`courierid` is not null then `courier`.`couriername` 
            when `franchisee`.`franchiseeid` is not null then `franchisee`.`franchiseename` 
            else `branch`.`branchname` end AS `dispatchername`,
            if(`order`.isverified=1,'Y','N') as isverified,
        if(`order`.isbillmade=1,'Y','N') as isbillmade,
        if(`order`.isoutcallconfirm=1,'Y','N') as isoutcallconfirm,
        if(`order`.ispaymentreceived=1,'Y','N') as ispaymentreceived,
        if(`order`.isreturn=1,'Y','N') as isreturn,
        if(deliverypriority=1,'Normal','Urgent') as deliverypriority,
        if(orderstatus=1,'Dispatch',if(orderstatus=2,'Do Not Dispatch',if(orderstatus=3,'Dispatch Till Date','Cancel'))) as orderstatus,
        if(specialinstruction=1,'Home Delivery','Office Hold') as specialinstruction,
 avexecutive.username,ocexecutive.username,bcexecutive.username,payexecutive.username,retexecutive.username
       
       from  `order`
       join `customer` on`customer`.`customerid` = `order`.`fkcustomerid`
       join `orderdetail` on`order`.`orderid` = `orderdetail`.`fkorderid` 
       join `callhour` on`customer`.`fkcallhourid` = `callhour`.`callhourid` 
       join `channel` on`customer`.`fkchannelid` = `channel`.`channelid` 
       join `paymentmode` on`order`.`fkpaymentmodeid` = `paymentmode`.`paymentmodeid` 
       join `callcenter` on`order`.`fkcallcenterid` = `callcenter`.`callcenterid` 
       join `extension` on`order`.`fkextensionid` = `extension`.`extensionid` 
       join `product` on`orderdetail`.`fkproductid` = `product`.`productid` 
       left join `franchisee` on`franchisee`.`franchiseeid` = `order`.`fkdispatcherid` 
       left join `courier` on`courier`.`courierid` = `order`.`fkdispatcherid` 
       left join `branch` on`branch`.`branchid` = `order`.`fkdispatcherid` 
       join `area` on`customer`.`fkareaid` = `area`.`areaid` 
       join `pincode` on`area`.`fkpincodeid` = `pincode`.`pincodeid` 
       join `city` on`area`.`fkcityid` = `city`.`cityid` 
       join `tehsil` on`city`.`fktehsilid` = `tehsil`.`tehsilid` 
       join `district` on`tehsil`.`fkdistrictid` = `district`.`districtid`      
       join `state` on`district`.`fkstateid` = `state`.`stateid`            
       join `country` on`state`.`fkcountryid` = `country`.`countryid`
       left join `user` avexecutive on avexecutive.userid=`order`.fkavexecutiveid
       left join `user` ocexecutive on ocexecutive .userid=`order`.fkavexecutiveid
       left join `user` bcexecutive on bcexecutive.userid=`order`.fkavexecutiveid
       left join `user` payexecutive on payexecutive.userid=`order`.fkavexecutiveid
       left join `user` retexecutive on retexecutive.userid=`order`.fkavexecutiveid
  order by `order`.`orderid`,`product`.`productid`

is five left join on same table user make it slow.?

left join `user` avexecutive on avexecutive.use rid=`order`.fka vexecutiveid
left join `user` ocexecutive on ocexecutive .userid=`order` .fkavexecutivei d
left join `user` bcexecutive on bcexecutive.use rid=`order`.fka vexecutiveid
left join `user` payexecutive on payexecutive.us erid=`order`.fk avexecutiveid
left join `user` retexecutive on retexecutive.us erid=`order`.fk avexecutiveid

[zmbd]

ipskalsi,
Just one table in the database?

[Rabbit]

It's hard to say anything without knowing anything about the data and its structure.

[ipskalsi]

Its has 1 parent table and many child tables. all the insert update delete fetch commands execute on parent table.

[zmbd]

Yes,
No wonder your query is so slow...... that'll take hours to read thru!

[ipskalsi]

is there is any option to make is fast.?

[Rabbit]

Good lord! You don't have just 5 outer joins. You have 15 inner joins and 8 outer joins. Every join will slow down a query. There's not much you can do about that except to create the appropriate indexes and join only on numeric fields. But even with the indexes and proper joins, 23 joins is going to be slow no matter what you do.

I seriously doubt you need a query of this size. You seem to want to pull in every single piece of information you can. But I doubt that the users need to see this level of detail. You really should break it up into more palatable chunks of data in a variety of reports.