Re: Index Not being used.

[Greg Mundt]

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