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

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,688
    Rep Power
    171

    A query question. Indexing issue I assume.


    Hi;

    Can someone please explain to me why it goes through all rows in the hotels table?

    Thank you


    Explain


    mysql Code:
    SELECT COUNT(*)    AS C,
           new_bookings.hotel_id,
           packages.id AS PID
    FROM   new_bookings
           INNER JOIN hotels
                   ON hotels.hotel_id = new_bookings.hotel_id
                      AND hotels.status = 'active'
           INNER JOIN packages
                   ON packages.hotel_id = hotels.hotel_id
    GROUP  BY hotels.hotel_id
    ORDER  BY c DESC
    LIMIT  15
    Code:
     CREATE TABLE `hotels`
      (
         `hotel_id`                            INT(10) UNSIGNED NOT NULL
         auto_increment,
         `beta_supplier`                       INT(11) NOT NULL,
         `commision`                           FLOAT NOT NULL,
         `allotz_id`                           INT(11) UNSIGNED NOT NULL,
         `hotelname`                           VARCHAR(200) NOT NULL DEFAULT '',
         `country_id`                          INT(10) UNSIGNED NOT NULL DEFAULT '0'
         ,
         `region_id`                           INT(10) UNSIGNED NOT NULL
         DEFAULT '0',
         `destination_id`                      INT(10) UNSIGNED NOT NULL DEFAULT '0'
         ,
         `places_id`                           INT(11) NOT NULL,
         `accomodationtype`                    VARCHAR(255) NOT NULL DEFAULT 'Hotel'
         ,
         `startype`                            VARCHAR(45) NOT NULL DEFAULT ''
         comment 'This would be image name',
         `ratingstd`                           VARCHAR(20) NOT NULL DEFAULT '',
         `abn`                                 VARCHAR(45) NOT NULL DEFAULT '',
         `address1`                            VARCHAR(200) NOT NULL DEFAULT '',
         `address2`                            VARCHAR(200) NOT NULL DEFAULT '',
         `town`                                VARCHAR(100) NOT NULL DEFAULT '',
         `postcode`                            VARCHAR(10) NOT NULL DEFAULT '',
         `adminphone`                          VARCHAR(100) NOT NULL DEFAULT '',
         `adminfax`                            VARCHAR(50) NOT NULL DEFAULT '',
         `acc_contactname`                     VARCHAR(100) NOT NULL DEFAULT '',
         `acc_phone`                           VARCHAR(100) NOT NULL DEFAULT '',
         `acc_fax`                             VARCHAR(50) NOT NULL DEFAULT '',
         `acc_email`                           VARCHAR(50) NOT NULL DEFAULT '',
         `res_contactname`                     VARCHAR(100) NOT NULL DEFAULT '',
         `res_phone`                           VARCHAR(100) NOT NULL DEFAULT '',
         `res_fax`                             VARCHAR(50) NOT NULL DEFAULT '',
         `res_email`                           VARCHAR(50) NOT NULL DEFAULT '',
         `sale_contactname`                    VARCHAR(100) NOT NULL DEFAULT '',
         `sale_phone`                          VARCHAR(100) NOT NULL DEFAULT '',
         `sale_email`                          VARCHAR(50) NOT NULL DEFAULT '',
         `mgm_contactname`                     VARCHAR(100) NOT NULL DEFAULT '',
         `mgm_phone`                           VARCHAR(50) NOT NULL DEFAULT '',
         `mgm_email`                           VARCHAR(50) NOT NULL DEFAULT '',
         `property_desc`                       TEXT,
         `quickdesc`                           TEXT NOT NULL,
         `noofrooms`                           INT(11) DEFAULT '0',
         `localattractions`                    TEXT,
         `businessfacilities`                  TEXT,
         `accept_child`                        VARCHAR(20) DEFAULT '0',
         `child_friendly`                      VARCHAR(20) DEFAULT '0',
         `nonsmoking`                          VARCHAR(20) DEFAULT '0',
         `businessfriendly`                    SMALLINT(6) DEFAULT '0',
         `amen_24hrrecp`                       SMALLINT(6) DEFAULT '0',
         `amen_lift`                           SMALLINT(6) DEFAULT '0',
         `amen_24hrroomsvc`                    SMALLINT(6) DEFAULT '0',
         `amen_messctr`                        SMALLINT(6) DEFAULT '0',
         `amen_pets`                           SMALLINT(6) DEFAULT '0',
         `amen_playgrd`                        SMALLINT(6) DEFAULT '0',
         `amen_bbq`                            SMALLINT(6) DEFAULT '0',
         `amen_heatedpool`                     SMALLINT(6) DEFAULT '0',
         `amen_busctr`                         SMALLINT(6) DEFAULT '0',
         `amen_outdoorpool`                    SMALLINT(6) DEFAULT '0',
         `amen_carhire`                        SMALLINT(6) DEFAULT '0',
         `amen_casino`                         SMALLINT(6) DEFAULT '0',
         `amen_transpt`                        SMALLINT(6) DEFAULT '0',
         `amen_chroom`                         SMALLINT(6) DEFAULT '0',
         `amen_rest`                           SMALLINT(6) DEFAULT '0',
         `amen_childclub`                      SMALLINT(6) DEFAULT '0',
         `amen_roomsvc`                        SMALLINT(6) DEFAULT '0',
         `amen_conci`                          SMALLINT(6) DEFAULT '0',
         `amen_safedepo`                       SMALLINT(6) DEFAULT '0',
         `amen_conference`                     SMALLINT(6) DEFAULT '0',
         `amen_sauna`                          SMALLINT(6) DEFAULT '0',
         `amen_securepark`                     SMALLINT(6) DEFAULT '0',
         `amen_spa`                            SMALLINT(6) DEFAULT '0',
         `amen_drycleaning`                    SMALLINT(6) DEFAULT '0',
         `amen_tab`                            SMALLINT(6) DEFAULT '0',
         `amen_games`                          SMALLINT(6) DEFAULT '0',
         `amen_tennis`                         SMALLINT(6) DEFAULT '0',
         `amen_kitchen`                        SMALLINT(6) DEFAULT '0',
         `amen_tourdesk`                       SMALLINT(6) DEFAULT '0',
         `amen_giftshop`                       SMALLINT(6) DEFAULT '0',
         `amen_valetpark`                      SMALLINT(6) DEFAULT '0',
         `amen_golf`                           SMALLINT(6) DEFAULT '0',
         `amen_vending`                        SMALLINT(6) DEFAULT '0',
         `amen_gym`                            SMALLINT(6) DEFAULT '0',
         `amen_water`                          SMALLINT(6) DEFAULT '0',
         `amen_interconn`                      SMALLINT(6) DEFAULT '0',
         `amen_cablesatt`                      SMALLINT(6) DEFAULT '0',
         `amen_internet`                       SMALLINT(6) NOT NULL DEFAULT '0',
         `checkintime`                         VARCHAR(6) NOT NULL DEFAULT '',
         `checkouttime`                        VARCHAR(6) NOT NULL DEFAULT '',
         `disttocityctr`                       DOUBLE(10, 2) NOT NULL DEFAULT '0.00'
         ,
         `nearestairport`                      VARCHAR(100) NOT NULL DEFAULT
         '',
         `instructions`                        TEXT,
         `distancetoairport`                   VARCHAR(255) NOT NULL DEFAULT '',
         `location`                            TEXT,
         `vehiclehire`                         TEXT,
         `carparking`                          TEXT,
         `airportpickup`                       SMALLINT(5) UNSIGNED NOT NULL DEFAULT
         '0',
         `pickupdetail`                        TEXT,
         `reserv_cancel_policy`                TEXT,
         `accountbalsettle`                    VARCHAR(100) NOT NULL DEFAULT '',
         `googlemap`                           VARCHAR(50) NOT NULL DEFAULT '',
         `username`                            VARCHAR(200) NOT NULL DEFAULT '',
         `password`                            VARCHAR(50) NOT NULL DEFAULT '',
         `mainimage`                           VARCHAR(50) NOT NULL DEFAULT '',
         `rest_alcbev`                         VARCHAR(50) NOT NULL DEFAULT '',
         `rest_name`                           VARCHAR(200) NOT NULL DEFAULT '',
         `rest_description`                    TEXT,
         `rest_menu`                           TEXT,
         `rest_openhour`                       VARCHAR(75) NOT NULL DEFAULT '',
         `rest_opendays`                       VARCHAR(100) NOT NULL DEFAULT '',
         `rest_image1`                         VARCHAR(50) NOT NULL DEFAULT '',
         `rest_image2`                         VARCHAR(50) NOT NULL DEFAULT '',
         `rest_image3`                         VARCHAR(50) NOT NULL DEFAULT '',
         `accbalsetdetail`                     TEXT,
         `amen_nighteveenter`                  SMALLINT(5) UNSIGNED DEFAULT '0',
         `pdf`                                 VARCHAR(50) NOT NULL DEFAULT '',
         `status`                              VARCHAR(10) NOT NULL DEFAULT '',
         `cancellationpolicy`                  TEXT NOT NULL,
         `paymentpolocy`                       TEXT NOT NULL,
         `dateadded`                           DATE NOT NULL DEFAULT '0000-00-00',
         `weekends`                            ENUM('2days', '3days') DEFAULT
         '2days',
         `locked`                              TINYINT(4) NOT NULL DEFAULT '1'
         comment 'Lock/Unlock flag',
         `last_alert`                          TIMESTAMP NOT NULL DEFAULT
         CURRENT_TIMESTAMP,
         `tags`                                TINYTEXT NOT NULL,
         `rank`                                TINYINT(4) UNSIGNED NOT NULL DEFAULT
         '0' comment 'TOP 10 Rank ',
         `share`                               TINYINT(1) NOT NULL DEFAULT '1',
         `step_one_complete`                   INT(11) NOT NULL,
         `step_two_complete`                   INT(11) NOT NULL,
         `step_three_complete`                 INT(11) NOT NULL,
         `step_four_complete`                  INT(11) NOT NULL,
         `history`                             TEXT NOT NULL,
         `sent_to_blog`                        INT(11) NOT NULL,
         `lat`                                 FLOAT NOT NULL,
         `lng`                                 FLOAT NOT NULL,
         `currency_code`                       VARCHAR(4) DEFAULT NULL,
         `language_code`                       VARCHAR(4) DEFAULT NULL,
         `banking_details`                     TEXT NOT NULL,
         `channel_manager_id`                  INT(11) NOT NULL,
         `channel_manager_name`                VARCHAR(255) NOT NULL,
         `channel_hotel_id`                    INT(11) NOT NULL,
         `hotel_id_in_channel_manager_website` INT(11) NOT NULL,
         `channel_hotel_status`                VARCHAR(255) NOT NULL,
         PRIMARY KEY (`hotel_id`),
         KEY `username` (`username`),
         KEY `hotels_ix_beta_supplier` (`beta_supplier`),
         KEY `hotelname_ix` (`hotelname`),
         KEY `status` (`status`),
         KEY `destination_id` (`destination_id`),
         KEY `hotelname_index` (`hotelname`),
         KEY `hotelstatus` (`status`)
      )
    engine=myisam
    auto_increment=10043
    DEFAULT charset=latin1
    Code:
     CREATE TABLE `new_bookings`
      (
         `id`                                    INT(11) NOT NULL auto_increment,
         `temp_cc`                               TEXT NOT NULL,
         `amount_saved_on_voucher`               DECIMAL(11, 2) NOT NULL,
         `extra_guest_charge_booking_all_nights` DECIMAL(11, 2) NOT NULL DEFAULT
         '0.00',
         `extra_guest_number`                    INT(11) NOT NULL DEFAULT '0',
         `extra_guest_charge_booking_per_night`  DECIMAL(11, 2) NOT NULL DEFAULT
         '0.00',
         `temp_email_session`                    VARCHAR(255) NOT NULL,
         `temp_mobile_session`                   VARCHAR(255) NOT NULL,
         `temp_phone_session`                    VARCHAR(255) NOT NULL,
         `temp_name_session`                     VARCHAR(255) NOT NULL,
         `temp_last_name_session`                VARCHAR(255) NOT NULL,
         `cc_encrypted`                          TEXT NOT NULL,
         `ip`                                    VARCHAR(100) NOT NULL,
         `previous_booking_id`                   VARCHAR(255) NOT NULL,
         `request_accepted_by`                   VARCHAR(250) NOT NULL,
         `request_accept_time`                   VARCHAR(250) NOT NULL,
         `decline_date_and_time`                 VARCHAR(255) NOT NULL,
         `cancel_date_and_time`                  VARCHAR(250) NOT NULL,
         `expiry_date_and_time`                  VARCHAR(250) NOT NULL,
         `expiry_date`                           VARCHAR(250) NOT NULL,
         `expiry_time`                           VARCHAR(250) NOT NULL,
         `type`                                  VARCHAR(255) NOT NULL,
         `time_added`                            VARCHAR(255) NOT NULL,
         `history`                               TEXT NOT NULL,
         `customer_id`                           INT(11) NOT NULL,
         `hotel_id`                              INT(11) NOT NULL,
         `room_id`                               INT(11) NOT NULL,
         `adults`                                INT(11) NOT NULL,
         `children`                              INT(11) NOT NULL,
         `children_age`                          VARCHAR(255) NOT NULL,
         `checkin_date`                          VARCHAR(255) NOT NULL,
         `checkout_date`                         VARCHAR(255) NOT NULL,
         `checkin_day`                           VARCHAR(255) NOT NULL,
         `checkout_day`                          VARCHAR(255) NOT NULL,
         `number_of_nights`                      INT(11) NOT NULL,
         `total_rate`                            DECIMAL(11, 2) NOT NULL DEFAULT
         '0.00',
         `extra_guest_charge_booking`            DECIMAL(11, 2) NOT NULL,
         `total_to_h2g`                          DECIMAL(11, 2) NOT NULL,
         `holding_deposit`                       DECIMAL(11, 2) NOT NULL,
         `booking_fee`                           DECIMAL(11, 2) NOT NULL,
         `balance_payable_to_hotel`              DECIMAL(11, 2) NOT NULL,
         `special_requests`                      TEXT,
         `status`                                VARCHAR(255) NOT NULL,
         `cancellation_notes`                    TEXT NOT NULL,
         `security_key`                          VARCHAR(50) NOT NULL,
         `source_website`                        VARCHAR(255) NOT NULL,
         `bargain_breaks_username`               VARCHAR(250) NOT NULL,
         `package`                               VARCHAR(250) NOT NULL,
         `estcheckintime`                        VARCHAR(250) NOT NULL,
         `notes_from_new_booking_form`           TEXT NOT NULL,
         `super`                                 INT(11) NOT NULL,
         `final_email_view_by_hotel`             TEXT NOT NULL,
         `final_email_view_by_customer`          VARCHAR(255) NOT NULL,
         `invoice_view_by_customer`              VARCHAR(255) NOT NULL,
         `number_of_rooms`                       INT(11) NOT NULL,
         `cancel_notes_to_customer`              TEXT NOT NULL,
         `cancel_notes_to_hotel`                 TEXT NOT NULL,
         `voucher_notes`                         TEXT NOT NULL,
         `if_started_as_book`                    INT(11) NOT NULL DEFAULT '0',
         PRIMARY KEY (`id`),
         KEY `status` (`status`),
         KEY `type` (`type`),
         KEY `customer_id` (`customer_id`),
         KEY `hotel_id` (`hotel_id`)
      )
    engine=myisam
    auto_increment=431976
    DEFAULT charset=latin1
    Code:
     CREATE TABLE `packages`
      (
         `id`                       INT(11) NOT NULL auto_increment,
         `onsale_box`               INT(11) NOT NULL DEFAULT '0',
         `package_order`            INT(1) NOT NULL,
         `hot_deal`                 INT(11) NOT NULL,
         `red_hot_deal`             INT(11) NOT NULL,
         `stop_sell`                INT(11) NOT NULL,
         `emails_receiver`          VARCHAR(255) NOT NULL,
         `status`                   VARCHAR(255) NOT NULL,
         `name`                     VARCHAR(55) NOT NULL,
         `minimum_number_of_nights` INT(11) NOT NULL,
         `type`                     VARCHAR(55) NOT NULL,
         `hotel_id`                 INT(11) NOT NULL,
         `room_id`                  INT(11) NOT NULL,
         `suppiler_id`              INT(11) NOT NULL,
         `edit_history`             TEXT NOT NULL,
         `added_by`                 VARCHAR(250) NOT NULL,
         `low`                      INT(11) NOT NULL,
         `shoulder`                 INT(11) NOT NULL,
         `high`                     INT(11) NOT NULL,
         `weekend_low`              INT(11) NOT NULL,
         `weekend_shoulder`         INT(11) NOT NULL,
         `weekend_high`             INT(11) NOT NULL,
         `special_events`           INT(11) NOT NULL,
         `date_added`               VARCHAR(55) NOT NULL,
         `description`              TEXT NOT NULL,
         `days_of_year`             VARCHAR(255) NOT NULL,
         `low_advertise`            VARCHAR(11) NOT NULL,
         `shoulder_advertise`       VARCHAR(11) NOT NULL,
         `high_advertise`           VARCHAR(11) NOT NULL,
         `special_advertise`        VARCHAR(11) NOT NULL,
         `low_min_nights`           INT(11) NOT NULL,
         `shoulder_min_nights`      INT(11) NOT NULL,
         `high_min_nights`          INT(11) NOT NULL,
         `special_min_nights`       INT(11) NOT NULL,
         `history`                  TEXT NOT NULL,
         `sent_to_blog`             INT(11) NOT NULL,
         `requirements`             TEXT NOT NULL,
         `start_date`               DATE NOT NULL,
         `end_date`                 DATE NOT NULL,
         PRIMARY KEY (`id`),
         KEY `hotel_id_index` (`hotel_id`),
         KEY `room_id` (`room_id`),
         KEY `type` (`type`),
         KEY `status` (`status`)
      )
    engine=myisam
    auto_increment=9635
    DEFAULT charset=latin1
  2. #2
  3. Mad Scientist
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2007
    Location
    North Yorkshire, UK
    Posts
    3,661
    Rep Power
    4124
    Can someone please explain to me why it goes through all rows in the hotels table?
    because you don't tell it NOT to.

    Run an explain query in mysql (basically put the keyword explain before your query and run it).


    I would have thought that having the and inside the inner join like you have would have helped limit at that stage, but without any further info my next attempt would be

    sql Code:
    SELECT
    	COUNT(*) AS C,
    	new_bookings.hotel_id,
    	packages.id AS PID
    FROM  
    	new_bookings
    INNER JOIN (
    		SELECT
    			hotels.hotel_id
    		FROM
    			hotels
    		WHERE
    			hotels.STATUS = 'active'
    	) h
    	ON
    	h.hotel_id = new_bookings.hotel_id
    INNER JOIN
    	packages
    	ON
    	packages.hotel_id = hotels.hotel_id
    GROUP BY
    	h.hotel_id
    ORDER BY
    	c DESC



    The count(*) may be ambiguous - what table do you want to count from? Maybe another level of nesting?

    sql Code:
    SELECT
    	COUNT(tmp.hotel_id) AS C,
    	tmp.hotel_id
    FROM (
    	SELECT
    		new_bookings.hotel_id,
    		packages.id AS PID
    	FROM  
    		new_bookings
    	INNER JOIN (
    			SELECT
    				hotels.hotel_id
    			FROM
    				hotels
    			WHERE
    				hotels.STATUS = 'active'
    		) h
    		ON
    		h.hotel_id = new_bookings.hotel_id
    	INNER JOIN
    		packages
    		ON
    		packages.hotel_id = hotels.hotel_id
    	) tmp
    GROUP BY
    	tmp.hotel_id
    ORDER BY
    	c DESC


    I'm just guessing now!!!!
    I said I didn't like ORM!!! <?php $this->model->update($this->request->resources[0])->set($this->request->getData())->getData('count'); ?>

    PDO vs mysql_* functions: Find a Migration Guide Here

    [ Xeneco - T'interweb Development ] - [ Are you a Help Vampire? ] - [ Read The manual! ] - [ W3 methods - GET, POST, etc ] - [ Web Design Hell ]
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2006
    Posts
    2,481
    Rep Power
    1752
    Originally Posted by Northie
    because you don't tell it NOT to.

    Run an explain query in mysql (basically put the keyword explain before your query and run it).
    He did! Just under his intro is the hyperlinked word 'Explain ' which opens up a png screen cap of the explain output. In essence it is usingindexes for the sub-selects but seems to go a 'little wibbly' on the main query

    Comments on this post

    • Northie agrees : didn't see the link ;)
    Last edited by SimonJM; August 8th, 2013 at 06:32 AM. Reason: Add in hyperlink from OP
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by Northie
    The count(*) may be ambiguous - what table do you want to count from?
    i'm afraid it doesn't work like that

    the FROM clause assembles an intermediate table consisting of joined rows

    COUNT(*) simply counts those intermediate rows (subject to grouping), so "what table do you want to count from" is misleading, because the answer is all of them
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by zxcvbnm
    Can someone please explain to me why it goes through all rows in the hotels table?
    because of the GROUP BY
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  10. #6
  11. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,688
    Rep Power
    171
    Originally Posted by r937
    because of the GROUP BY
    hi
    1- How can I get total number of bookings per hotel'? My solution as you can see makes the system very slow.
    2- Is Northi's solution better practise than mine? If so, why?
    Thank you
  12. #7
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by zxcvbnm
    1- How can I get total number of bookings per hotel'?
    i'm not sure

    i don't understand the (non-standard) mismatch between your GROUP BY and your SELECT clauses

    Originally Posted by zxcvbnm
    2- Is Northi's solution better practise than mine?
    insofar as adherence to standards is concerned, yes, his second query removes that mismatch

    but the business of wrapping stuff in subqueries, that's just artistry, not better practice
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  14. #8
  15. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,688
    Rep Power
    171
    Originally Posted by r937
    i'm not sure

    i don't understand the (non-standard) mismatch between your GROUP BY and your SELECT clauses
    Hi;

    Each record in new_bookings table has a hotel_id.

    I want to see how many bookings each hotel has and sort results by that number.

  16. #9
  17. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by zxcvbnm
    I want to see how many bookings each hotel has and sort results by that number.
    so what's the deal with the package?

    you can simplify your query immensely by removing that table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  18. #10
  19. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,688
    Rep Power
    171
    Originally Posted by r937
    so what's the deal with the package?

    you can simplify your query immensely by removing that table
    No arguments there. But still scans all hotels. What am I doing wrong Rudy?
    Code:
    SELECT COUNT(*)    AS C,
           new_bookings.hotel_id
           FROM   new_bookings
           INNER JOIN hotels
                   ON hotels.hotel_id = new_bookings.hotel_id
                      AND hotels.status = 'active'
    GROUP  BY hotels.hotel_id
    ORDER  BY c DESC
    LIMIT  15
  20. #11
  21. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by zxcvbnm
    But still scans all hotels.
    how is the optimizer going to decide which hotels to return? apparently the status index is not specific enough, so it decides that a scan is necessary

    try declaring an index on {id,status} and see if that helps

    by the way, you have two sets of duplicate indexes, that won't affect SELECTs but slows down any INSERTs and DELETEs
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  22. #12
  23. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,688
    Rep Power
    171
    Originally Posted by r937
    how is the optimizer going to decide which hotels to return? apparently the status index is not specific enough, so it decides that a scan is necessary

    try declaring an index on {id,status} and see if that helps
    Didnt help
    Code:
    id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
    1 	SIMPLE 	hotels 	index 	PRIMARY,status,hotelstatus,id_stat 	PRIMARY 	4 	NULL	1867 	Using where; Using temporary; Using filesort
    1 	SIMPLE 	new_bookings 	ref 	hotel_id 	hotel_id 	4 	h2g.hotels.hotel_id 	294 	Using where; Using index
  24. #13
  25. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    3
    Rep Power
    0

    (psst, guys, this is zxc-- Tea too, posting while he was locked out --requinix)


    What about
    Code:
    SELECT COUNT(*) AS C,
           nb.hotel_id,
           p.id     AS PID
    FROM   new_bookings nb
           INNER JOIN hotels h
                   ON h.hotel_id = nb.hotel_id
                      AND h.status = 'active'
           INNER JOIN packages p
                   ON p.hotel_id = h.hotel_id
                      AND p.status = 'active'
    WHERE  nb.status = 'Deposit Paid'
    GROUP  BY nb.hotel_id
    ORDER  BY c DESC
    LIMIT  15
    Last edited by requinix; August 20th, 2013 at 12:22 AM.

IMN logo majestic logo threadwatch logo seochat tools logo