what is the best way to optimize join query with multiple AND's?
I'm developing an online reservation system where people can reserve items based on availability for a particular hour of the day. For that i'm using two tables 1.Item 2.Reservation
Item:(InnoDB) ------------------------- id INT (PRIMARY) category_id MEDIUMINT name VARCHAR(20) make VARCHAR(20) availability ENUM('1','0') Reservation:(InnoDB) ------------------------- id INT (PRIMARY) date DATE Item_id INT slot VARCHAR(50) SELECT Item.id,Item.category,Item.make,Item.name,reservation.slot FROM Item INNER JOIN reservation ON Item.id=reservation.Item_id AND Item.category_id=2 AND Item.availability=1 AND reservation.date = DATE(NOW());
I'm using the above query to display all the items under a particular category with free timeslots which a user can reserve on a particular date.
slot field in reservation table contains string(ex:0:1:1:1:0:0:0:0:0:0:0:0:1:1:1:0:0:0:0:0:0:0:0:0) where 1 means that hour is reserved and 0 means available.
availability in Item table shows wether that item is available for reservation or not(may be down for servicing).
First of all is my table structure fine ?Secondly what is the best way to optimize my query(multi column indexing etc).
Put a foreign key constraint and index on your FK, this should speed things up a little. You appear to mix INT for the item ID and MEDIUMINT for the FK, not sure this is what nature intended.
Points to remember while choosing an Attribute on which an Index will be created
A column that is frequently used in a SELECT list and a WHERE clause. A column in which data will be accessed in sequence by a range of values. A column that will be used with the GROUP By or ORDER BY clause to sort data. A column used in a join, such as the FOREIGN KEY column. A column that is used as a PRIMERY KEY. Try to create index on numeric values. can introduce a surrogate key if no numeric pK is there.
Why are you putting all of those conditions in the join clause? Why not:
SELECT Item.id,Item.category,Item.make,Item.name,reservation.slot FROM Item INNER JOIN reservation ON Item.id=reservation.Item_id WHERE Item.category_id=2 AND Item.availability=1 AND reservation.date = DATE(NOW());
I'm not enough of a SQL expert to say whether this will make it faster, but it looks more obvious to me.
I would suggest creating an index on Reservation.item_id. That should help you improve query performance.