Delayed page loads due to heavy sqls /temp tables or views

[s4surbhi2218]

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.

[gizmola]

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.

[s4surbhi2218]

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 .

[Illusion]

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()) ;