Re: Help optimizing query/index

[Chris T]

Sure here's my table ddl's:

reservations -

CREATE TABLE
    reservations
    (
        property_id CHAR(4) NOT NULL,
        crsresnr VARCHAR(16) NOT NULL,
        accountuid VARCHAR(40) NOT NULL,
        arrival DATE,
        departure DATE,
        nights VARCHAR(3),
        status VARCHAR(8),
        roomtype VARCHAR(10),
        ratecode VARCHAR(14) DEFAULT '0.00',
        roomrate DECIMAL(4,2) DEFAULT '0.00',
        roomrev DECIMAL(7,2) DEFAULT '0.00',
        roomnet DECIMAL(7,2) DEFAULT '0.00',
        roomtax DECIMAL(7,2) DEFAULT '0.00',
        roomnr VARCHAR(5),
        companyuid VARCHAR(40),
        receivablesuid VARCHAR(40),
        groupuid VARCHAR(40),
        traveluid VARCHAR(40),
        membernr VARCHAR(24),
        gtd VARCHAR(6),
        adult INT(2) DEFAULT '0',
        child INT(2) DEFAULT '0',
        source VARCHAR(12),
        lastupdate DATETIME,
        PRIMARY KEY (accountuid, property_id),
        CONSTRAINT FK_reservations_property_id FOREIGN KEY (property_id) REFERENCES property_info
        (property_id),
        CONSTRAINT IX_reservations_extract_dup UNIQUE (property_id, accountuid, lastupdate),
        INDEX IX_reservations_search_date (departure, arrival, property_id),
        INDEX IX_reservations_roomstat (property_id, departure, roomtype),
        INDEX IX_reservations_ratestat (property_id, departure, ratecode),
        INDEX IX_reservations_lastupdate (lastupdate),
        INDEX IX_reservations_ratesearch (property_id, ratecode)
    )
    ENGINE=InnoDB DEFAULT CHARSET=latin1

property_id -

CREATE TABLE
    property_info
    (
        property_id CHAR(3) NOT NULL,
        city INT NOT NULL,
        state VARCHAR(3) NOT NULL,
        location_name VARCHAR(60),
        owner INT(1) DEFAULT '1' NOT NULL,
        district INT(3) DEFAULT '0' NOT NULL,
        status INT(1) DEFAULT '1' NOT NULL,
        loan INT(1) DEFAULT '7' NOT NULL,
        email VARCHAR(30) DEFAULT '[e-mail]' NOT NULL,
        timezone INT(1) DEFAULT '5' NOT NULL,
        historical INT(1) DEFAULT '0' NOT NULL,
        sdate DATE DEFAULT '2011-01-01' NOT NULL,
        edate DATE DEFAULT '2011-12-31' NOT NULL,
        data_update TINYINT(1) DEFAULT '0',
        country VARCHAR(3) DEFAULT 'US' NOT NULL,
        region INT(2),
        comments text,
        PRIMARY KEY (property_id),
        CONSTRAINT property_district_fk FOREIGN KEY (district) REFERENCES property_district
        (district_id) ,
        CONSTRAINT property_loan_loan_id_fk FOREIGN KEY (loan) REFERENCES property_loan (loan_id) ,
        CONSTRAINT property_ownership_ownership_id_fk FOREIGN KEY (owner) REFERENCES
        property_ownership (owner_id) ,
        CONSTRAINT property_status_status_id_fk FOREIGN KEY (status) REFERENCES property_status
        (status_id),
        INDEX property_location_location_id_fk (city),
        INDEX property_ownership_ownership_id_fk (owner),
        INDEX property_status_status_id_fk (status),
        INDEX property_loan_loan_id_fk (loan),
        INDEX property_district_fk (district)
    )
    ENGINE=InnoDB DEFAULT CHARSET=latin1

ractecodes_map -

CREATE TABLE
    ratecodes_map
    (
        map_id INT(2) NOT NULL AUTO_INCREMENT,
        rate_code VARCHAR(20),
        rate_desc VARCHAR(80),
        enabled TINYINT(1) DEFAULT '1' NOT NULL,
        PRIMARY KEY (map_id)
    )
    ENGINE=InnoDB DEFAULT CHARSET=latin1