With this typical query, are these indexes on my InnoDB table correct or should I change them?

Question

I have a php query that runs fairly often like this one:

$query = 'SELECT * FROM work_orders '
    .'WHERE '    
        . "((end_time >= ?"
  . "AND start_time <= ?) "
        . "OR (start_time <= ? "
  . "AND end_time >= ? ) "
        . "OR (start_time >= ? "
  . "AND end_time <= ? )) ";

And a table defined as:

CREATE TABLE IF NOT EXISTS `work_orders` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `work_order_number` varchar(32) COLLATE latin1_general_ci NOT NULL,
  `start_time` datetime NOT NULL,
  `end_time` datetime NOT NULL,
  `client_name` varchar(128) COLLATE latin1_general_ci NOT NULL,
  `location` varchar(128) COLLATE latin1_general_ci NOT NULL,
  `note` varchar(255) COLLATE latin1_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `note_idx` (`note`),
  KEY `client_idx` (`client_name`),
  KEY `location_idx` (`location`),
  KEY `start_time_idx` (`start_time`),
  KEY `end_time_idx` (`end_time`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=1 ;

I'm often confused by how I should create indexes. This is a read heavy table with lots of searching on the data, which is why I have each column indexed, but by far the query most often run uses the 3 combinations of start and end date to determine if a work order falls in a particular calendar range. Should I have an index on start_time and end_time individually, as I currently do, or should I create a composite key out of the two? Is there a better way to set the indexes up in general? Should I even be using InnoDB? I'm not using transactions at all.

Answer 1

This answer is two-fold. First of all, I would make the index like:

KEY start_time_idx (start_time, end_time)

BUT (and this is the second issue): You query will not be able to use any of the indexes. Here's why:

SELECT *
FROM work_orders
WHERE (
  (end_time >= <some-date> AND start_time <= <some-date>)
  OR
  (end_time >= <some-date> AND start_time <= <some-date>)
  OR
  (end_time >= <some-date> AND start_time <= <some-date>)
)

As soon as you are using the OR statement, you are effectively disabling the use of indexes on the fields involved in the OR statement. Since your WHERE statement has no other fields that is not involved in the OR statement, no index will be used.

I quess that this is executed in a PHP script. Do you have access to phpMyAdmin? Then try to run this query prepended by an EXPLAIN. It will give you some hints.

If the table contains a lot of data, you might want to change this query into 3 queries, each of which only queries for a single date range, then concatenating the result in PHP afterwards.

/Carsten