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).

thanks, ravi.

Answers


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.


Need Your Help

Concatenating strings in PROGMEM for arduino UNO

c++ arrays string arduino progmem

I am trying to create a list of wavefile names soundList to play through an adafruit WAV shield ontop an arduino UNO. Since these file names are never going to change, and I am running out of sRam,...