MySQL Forums :: Performance :: Index Not being used.


Advanced Search

Re: Index Not being used.
Posted by: Greg Mundt ()
Date: June 01, 2016 05:40AM

Okay, I'll try to clarify a little bit here.

SELECT
COUNT(*)
FROM
kilwins_pos.pos_ord_headers AS H
WHERE H.ORD_Date BETWEEN '2015-01-01' AND '2015-12-31'

This select alone takes 94 seconds to complete. Header is only used because it contains the Order Date. There are no dates on the line fields. This is where I was getting the number of header records selected within the date range. What I'd like to get is list of items sold within that date range so I need to join headers and lines so I can select by date for all stores. I can then start expanding out by using groups. So first get a total, then group by item and group by store. (Item is in lines, Store is in header).

I think the general question is how do I figure out where my performance bottle necks are and if adding indexes will help me.

pos_ord_headers - PRIMARY - ORD_ID; Store_Date - ORD_Store, ORD_Date; ORD_Date - Ord_Date
pos_ord_lines - PRIMARY - ORD_ID, ORD_Line; Items - ORD_Item; Item_Classes - ORD_Item_Class

Options: ReplyQuote


Subject Views Written By Posted
Index Not being used. 680 Greg Mundt 05/26/2016 02:29PM
Re: Index Not being used. 355 Peter Brawley 05/26/2016 03:53PM
Re: Index Not being used. 369 Øystein Grøvlen 05/27/2016 12:45AM
Re: Index Not being used. 337 Greg Mundt 05/27/2016 07:05AM
Re: Index Not being used. 327 Rick James 05/27/2016 11:29PM
Re: Index Not being used. 329 Greg Mundt 05/31/2016 07:04AM
Re: Index Not being used. 326 Rick James 05/31/2016 09:26PM
Re: Index Not being used. 364 Greg Mundt 06/01/2016 05:40AM
Re: Index Not being used. 303 Rick James 06/01/2016 11:29AM
Re: Index Not being used. 352 Greg Mundt 06/01/2016 11:41AM
Re: Index Not being used. 354 Rick James 06/04/2016 05:14PM
Re: Index Not being used. 358 Greg Mundt 05/27/2016 10:14AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.