s4surbhi2218 Posted January 28, 2013 Share Posted January 28, 2013 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. Link to comment https://forums.phpfreaks.com/topic/273721-delayed-page-loads-due-to-heavy-sqls-temp-tables-or-views/ Share on other sites More sharing options...
gizmola Posted January 28, 2013 Share Posted January 28, 2013 I see a number of date related comparisons in your where clauses, for example: and date_format(tblOM.CreatedOn,'%H') >= '".$StartTime."' " . When you use a function on a column like this, you insure that mysql will NOT use an index. Link to comment https://forums.phpfreaks.com/topic/273721-delayed-page-loads-due-to-heavy-sqls-temp-tables-or-views/#findComment-1408689 Share on other sites More sharing options...
s4surbhi2218 Posted January 28, 2013 Author Share Posted January 28, 2013 I see a number of date related comparisons in your where clauses, for example: and date_format(tblOM.CreatedOn,'%H') >= '".$StartTime."' " . When you use a function on a column like this, you insure that mysql will NOT use an index. sorry , did not get ur point . Link to comment https://forums.phpfreaks.com/topic/273721-delayed-page-loads-due-to-heavy-sqls-temp-tables-or-views/#findComment-1408692 Share on other sites More sharing options...
Illusion Posted January 28, 2013 Share Posted January 28, 2013 check this http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_hour and you don't need use date_format in condition date_format(tblOM.CreatedOn,'%Y-%m-%d')=curdate()) ; Link to comment https://forums.phpfreaks.com/topic/273721-delayed-page-loads-due-to-heavy-sqls-temp-tables-or-views/#findComment-1408776 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.