MySQL Forums
Forum List  »  Performance

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
1052
May 26, 2016 02:29PM
537
May 26, 2016 03:53PM
498
May 27, 2016 12:45AM
475
May 27, 2016 07:05AM
429
May 27, 2016 11:29PM
564
May 31, 2016 07:04AM
441
May 31, 2016 09:26PM
Re: Index Not being used.
516
June 01, 2016 05:40AM
386
June 01, 2016 11:29AM
438
June 01, 2016 11:41AM
446
June 04, 2016 05:14PM
505
May 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.