MySQL booking site: query/db optimization

Question

I have a very bad performance in most of my queries. I've read a lot on stackoverflow, but still have some questions, maybe anyone could help or give me any hints?

Basically, i am working on a booking website, having among others the following tables:

objects

+----+---------+--------+---------+------------+-------------+----------+----------+-------------+------------+-------+-------------+------+-----------+----------+-----+-----+
| id | user_id | status | type_id | privacy_id | location_id | address1 | address2 | object_name | short_name | price | currency_id | size | no_people | min_stay | lat | lng |
+----+---------+--------+---------+------------+-------------+----------+----------+-------------+------------+-------+-------------+------+-----------+----------+-----+-----+

OR in MySQL:

CREATE TABLE IF NOT EXISTS `objects` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'object_id',
  `user_id` int(11) unsigned DEFAULT NULL,
  `status` tinyint(2) unsigned NOT NULL,
  `type_id` tinyint(3) unsigned DEFAULT NULL COMMENT 'type of object, from object_type id',
  `privacy_id` tinyint(11) unsigned NOT NULL COMMENT 'id from privacy',
  `location_id` int(11) unsigned DEFAULT NULL,
  `address1` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `address2` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `object_name` varchar(35) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'given name by user',
  `short_name` varchar(12) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'short name, selected by user',
  `price` int(6) unsigned DEFAULT NULL,
  `currency_id` tinyint(3) unsigned DEFAULT NULL,
  `size` int(4) unsigned DEFAULT NULL COMMENT 'size rounded and in m2',
  `no_people` tinyint(3) unsigned DEFAULT NULL COMMENT 'number of people',
  `min_stay` tinyint(2) unsigned DEFAULT NULL COMMENT '0=no min stay;else # nights',
  `lat` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
  `lng` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1451046 ;

reservations

+----+------------+-----------+-----------+---------+--------+
| id | by_user_id | object_id | from_date | to_date | status |
+----+------------+-----------+-----------+---------+--------+

OR in MySQL:

CREATE TABLE IF NOT EXISTS `reservations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `by_user_id` int(11) NOT NULL COMMENT 'user_id of guest',
  `object_id` int(11) NOT NULL COMMENT 'id of object',
  `from_date` date NOT NULL COMMENT 'start date of reservation',
  `to_date` date NOT NULL COMMENT 'end date of reservation',
  `status` int(1) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=890729 ;

There are a few questions:

1 - I have not set any additional key (except primary) - where should I set and which key should I set?

2 - I have read about MyISAM vs InnoDB, the conclusion for me was that MyISAM is faster when it comes to read-only, whereas InnoDB is designed for tables that get UPDATED or INSERTs more frequently. So, currently objects uses MyISAM and reservations InnoDB. Is this a good idea to mix? Is there a better choice?

3 - I need to query those objects that are available in a certain period (between from_date and end_date). I have read (among others) this post on stackoverflow: MySQL select rows where date not between date

However, when I use the suggested solution the query times out before returning any results (so it is really slow):

SELECT DISTINCT o.id FROM objects o LEFT JOIN reservations r ON(r.object_id=o.id) WHERE

COALESCE('2012-04-05' NOT BETWEEN r.from_date AND r.to_date, TRUE)
AND COALESCE('2012-04-08' NOT BETWEEN r.from_date AND r.to_date, TRUE)
AND o.location_id=201

LIMIT 20

What am I doing wrong? What is the best solution for doing such a query? How do other sites do it? Is my database structure not the best for this or is it only the query?

I would have some more questions, but I would be really grateful for getting any help on this! Thank you very much in advance for any hint or suggestion!

Answer 1

It appears you are looking for any "objects" that do NOT have a reservation conflict based on the from/to dates provided. Doing a coalesce() to always include those that are not ever found in reservations is an ok choice, however, being a left-join, I would try left joining where the IS a date found, and ignoring any objects FOUND. Something like

SELECT DISTINCT 
      o.id 
   FROM 
      objects o 
         LEFT JOIN reservations r 
            ON o.id = r.object_id
           AND (  r.from_date between '2012-04-05' and '2012-04-08'
               OR r.to_date between '2012-04-05' and '2012-04-08' )
   WHERE
          o.location_id = 201
      AND r.object_id IS NULL
  LIMIT 20

I would ensure an index on the reservations table by (object_id, from_date ) and another (object_id, to_date). By explicitly using the from_date between range, (and to date also), you are specifically looking FOR a reservation occupying this time period. If they ARE found, then don't allow, hence the WHERE clause looking for "r.object_id IS NULL" (ie: nothing is found in conflict within the date range you've provided)

Expanding from my previous answer, and by having two distinct indexes on (id, from date) and (id, to date), you MIGHT get better performance by joining on reservations for each index respectively and expecting NULL in BOTH reservation sets...

SELECT DISTINCT 
      o.id 
   FROM 
      objects o 
         LEFT JOIN reservations r 
            ON o.id = r.object_id
           AND r.from_date between '2012-04-05' and '2012-04-08'
         LEFT JOIN reservations r2 
            ON o.id = r2.object_id
           AND r2.to_date between '2012-04-05' and '2012-04-08'
   WHERE
          o.location_id = 201
      AND r.object_id IS NULL
      AND r2.object_id IS NULL
  LIMIT 20

Answer 2

I wouldn't mix InnoDB and MyISAM tables, but I would define all the tables as InnoDB (for foreing keys support). Generally all the columns with the _id suffix should be foreign keys refering to appropriate table (object_id => objects etc).

You don't have to define index on foreign key as it is defined automatically (since MySQL 4.1.2), but you can define additional indexes on reservations.from_date and reservations.to_date columns for faster comparison.