Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2004
    Posts
    20

    Unanswered: Optimize SQL query

    Hello,

    I have the following SQL query which finds the next available time for a reservation from the next 7 days based on the amount of needed timeslots:

    Code:
    SELECT  
        `s`.`time` ,
        `s`.`timeslot` AS start_timeslot, 
        MAX(  `s2`.`timeslot` ) AS end_timeslot, 
        MAX(  `s2`.`time` ) AS end_time,  
        `s`.`employee_id` 
    FROM  
        `demodatabase`.`time_slot` s
    INNER JOIN  
        `demodatabase`.`time_slot` s2 
    ON 
        DATE(  `s2`.`time` ) = DATE(  `s`.`time` ) 
    AND  
        `s2`.`timeslot` BETWEEN  `s`.`timeslot` 
    AND  
        `s`.`timeslot` +9 -1
    WHERE  
        `s`.`booked` =0
        AND  `s2`.`booked` =0
        AND  `s`.`employee_id` =  `s2`.`employee_id` 
        AND  `s`.`employee_id` =1
        AND  `s`.`time` >=  '2013-05-08 16:42:22'
        AND  `s`.`time` >=  '2013-05-08 16:42:22'
        AND  `s`.`time` <=  '2013-05-15 16:42:22'
        AND MINUTE(  `s`.`time` ) 
        IN ( 00, 15, 30, 45 ) 
    GROUP BY 
        DATE(  `s`.`time` ) ,  `s`.`timeslot` 
    HAVING 
        COUNT( * ) =9
    LIMIT 1
    The table structure is as follows:

    Code:
    CREATE TABLE IF NOT EXISTS `time_slot` (
      `id` char(36) NOT NULL,
      `timeslot` int(11) NOT NULL,
      `time` datetime NOT NULL,
      `booked` tinyint(1) NOT NULL,
      `employee_id` int(11) NOT NULL,
      `reservation_id` int(11) DEFAULT NULL,
      `lunch_time` tinyint(1) DEFAULT NULL,
      `absence_id` int(11) DEFAULT NULL,
      `office_closed_id` int(11) DEFAULT NULL,
      `public_holiday_id` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `time_timeslot` (`time`,`timeslot`),
      KEY `time` (`time`),
      KEY `timeslot` (`timeslot`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    And it has indexes on time & time_slot column and time and time_slot separately.

    Is there a way to optimize this query better? it takes quite a lot of time to process.
    Last edited by doze; 05-08-13 at 10:57.

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    You should take a look at your data. I suspect that an index on (employee_id, booked and time) together would give you sufficiently low cardinality that it would return your results from a large number of rows sufficiently quickly.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •