#1
  1. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Nobbies beach, Gold Coast. It's beautiful.
    Posts
    2,575
    Rep Power
    171

    INSERT SELECT takes a long time to run


    Hello;

    I was wondering if I can somehow optimise this query. It takes such a long time to execute. So long that the browser times out!
    EXPLAIN
    Thank you.
    Code:
     INSERT INTO allotments_new
                (package_id,
                 room_id,
                 hotel_id,
                 date_added,
                 day_date,
                 day_rate,
                 number_of_rooms,
                 status,
                 supplier_id,
                 day_of_the_week)
    SELECT packages.id,
           packages.room_id,
           packages.hotel_id,
           Curdate(),
           annual_calendar.day_date,
           low,
           '0',
           'b',
           hotels.beta_supplier,
           Dayname(annual_calendar.day_date)
    FROM   packages
           INNER JOIN hotels
                   ON hotels.hotel_id = packages.hotel_id
                      AND hotels.status = 'active'
           INNER JOIN hotelrooms
                   ON hotelrooms.room_id = packages.room_id
                      AND hotelrooms.status = 'active'
           LEFT OUTER JOIN calendars_new
                        ON calendars_new.hotel_id = hotels.hotel_id
                           AND calendars_new.day_date BETWEEN
                               '2013-02-08' AND '2014-02-08'
           LEFT OUTER JOIN allotments_new
                        ON allotments_new.package_id = packages.id
                           AND allotments_new.day_date BETWEEN
                               '2013-02-08' AND '2014-02-08'
                           AND calendars_new.day_date = allotments_new.day_date
           LEFT OUTER JOIN annual_calendar
                        ON annual_calendar.day_date = calendars_new.day_date
                           AND annual_calendar.day_date BETWEEN
                               '2013-02-08' AND '2014-02-08'
    WHERE  packages.status = 'active'
           AND packages.hotel_id > 1800
           AND allotments_new.day_date IS NULL
    Last edited by zxcvbnm; February 7th, 2013 at 09:13 PM.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    for calendars_new and allotments_new, create composite indexes on (hotel_id,day_date) and (package_id,day_date) respectively

    but i'm only guessing as i'm not a performance expert
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  4. #3
  5. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Nobbies beach, Gold Coast. It's beautiful.
    Posts
    2,575
    Rep Power
    171
    Originally Posted by r937
    for calendars_new and allotments_new, create composite indexes on (hotel_id,day_date) and (package_id,day_date) respectively

    but i'm only guessing as i'm not a performance expert
    OH!!!!!It took 5 seconds!!!

    Rudy Rudy Rudy Rudy
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    Originally Posted by zxcvbnm
    Rudy Rudy Rudy Rudy
    it's nice to be appreciated

    even if it's just a bromance

    r937.com | rudy.ca
    please visit Simply SQL and buy my book

IMN logo majestic logo threadwatch logo seochat tools logo