Search the Community

Hi All, I am facing Delayed page loads due to heavy sqls. I am using a temp table and show processlist shows me that delay in page load is due to sending data to Insert into tmpTodayOrderPerHour (Select coalesce(Count(distinct tblOM.OrderID),'0') as 'NoOforder' ,'".$StartTime."' as 'NoOfHour' from tblOrderMaster tblOM Join tblOrderDetail tblOD ON tblOD.OrderID = tblOM.OrderID where tblOD.Quantity > 0 and tblOD.Status!='' and tblOD.Status!='12' and tblOD.Status IS NOT NULL and date_format(tblOM.CreatedOn,'%H') >= '".$StartTime."' " . " and date_format(tblOM.CreatedOn,'%H') < '".$dateDiff."'". "and tblOD.CompanyID='".$this->iCompanyID."' and date_format(tblOM.CreatedOn,'%Y-%m-%d')=curdate()) ; Where my tblOrderMaster has 654078 records and tblOrderDeatil has 963808 records. what i beleive is that prob is not due to temp table but due to heavy data being traversed in the sql select. Due to some other optimization i created the following indexes /*Index Information*/ --------------------- Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment -------------- ---------- ---------------- ------------ ----------- --------- ----------- -------- ------ ------ ---------- --------- tblOrderMaster 0 PRIMARY 1 OrderID A 658011 (NULL) (NULL) BTREE tblOrderMaster 1 idx_SearchOrder1 1 CustomerID A 131602 (NULL) (NULL) BTREE tblOrderMaster 1 idx_Om1 1 OrderID A 658011 (NULL) (NULL) BTREE tblOrderMaster 1 idx_Om1 2 CreatedOn A 658011 (NULL) (NULL) YES BTREE tblOrderMaster 1 idx_Om2 1 OrderID A 658011 (NULL) (NULL) BTREE tblOrderMaster 1 idx_Om2 2 CreatedOn A 658011 (NULL) (NULL) YES BTREE tblOrderMaster 1 idx_Om2 3 CompanyID A 658011 (NULL) (NULL) BTREE /*Index Information*/ --------------------- Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment -------------- ---------- ------------------------ ------------ ---------------- --------- ----------- -------- ------ ------ ---------- --------- tblOrderDetail 0 PRIMARY 1 OrderDetailID A 979589 (NULL) (NULL) BTREE tblOrderDetail 1 idx_RecurrenceOrderToday 1 RecurrenceNumber A 18 (NULL) (NULL) YES BTREE tblOrderDetail 1 idx_CaptureOrderStat 1 Status A 18 (NULL) (NULL) YES BTREE tblOrderDetail 1 idx_tblOrderDetail_Batch 1 Status A 18 (NULL) (NULL) YES BTREE tblOrderDetail 1 idx_tblOrderDetail_Batch 2 OrderID A 979589 (NULL) (NULL) BTREE tblOrderDetail 1 idx_OrderDetailException 1 OrderID A 979589 (NULL) (NULL) BTREE tblOrderDetail 1 idx_OrderDetailException 2 Status A 979589 (NULL) (NULL) YES BTREE tblOrderDetail 1 idx_Od1 1 Quantity A 18 (NULL) (NULL) YES BTREE tblOrderDetail 1 idx_Od1 2 Status A 18 (NULL) (NULL) YES BTREE Now i have two thoughts 1. Make new indexes for the select sql , if this is the right direction in which m thinking then what should be my criteria of choosing them? i am unable to comprehend Describe Possible keys for TblOrderDetrail idx_CaptureOrderStat,idx_tblOrderDetail_Batch,idx_OrderDetailException,idx_Od1 possible keys for tblOrderMAster PRIMARY,idx_Om1,idx_Om2 2.the other idea is making views insted of temp table but would it help??? Please suggest.