Lets say I have 3 tables: orders, order items, and items
Orders is a table that describes orders obviously. It has information such as order date customer id ect. Items is a table that describes items it contains info such as product name and inventory. Order items could be compared to a shopping cart it basically describes all the items the order was placed for. So far so good, right?
Well I need to make a reporting tool that will basically query the order items table and give me all of the order items that are for a given item and happened between a given time frame. Just to reiterate, before the query starts I know: the item title and a timeframe I'm interested in.
so here's my query currently in sort of psuedo-sql
select * from Order_Item where Order.orderDate <= ? and Order.orderDate >= ? and Item.name in (?)
This query works great it gets everything that I need until I try to run it on my production environment. Once I do that, it about 40 seconds to run. Now let's talk about indexes. The order table has an index for orderDate. The item table has an index for name, and the order_item table has an index for (orderId, itemId). Now obviously there's a huge red flag about this query in terms of optimization "select *." For all intents and purposes, lets ignore that fact. My real question is is there anything I could do to the query itself to increase my runtime performance.
I really don't know how to say this the right way, but adding indexes to the tables is an option. Ergo, if there are any suggestions on that front I'm all ears.
I just realized how bad my title is. I cant figure out how to change it though. Sorry guys.