Форум пользователей MySQL

CREATE TABLE `tez_spo_rows` (
  `id` bigint(10) NOT NULL AUTO_INCREMENT,
  `id_spo` int(7) NOT NULL DEFAULT '0',
  `country` smallint(4) NOT NULL DEFAULT '1104',
  `sporegionset` int(9) NOT NULL DEFAULT '0',
  `number` int(4) NOT NULL DEFAULT '0',
  `hotelroomtype` mediumint(7) NOT NULL DEFAULT '0',
  `startdate` date NOT NULL DEFAULT '0000-00-00',
  `nightcount` tinyint(2) NOT NULL DEFAULT '0',
  `hotelpansion` mediumint(7) NOT NULL DEFAULT '0',
  `staytype` int(6) NOT NULL DEFAULT '0',
  `grouptypeage` int(7) NOT NULL DEFAULT '0',
  `hotelstop` enum('N','Y') NOT NULL DEFAULT 'N',
  `price` int(5) NOT NULL DEFAULT '0',
  `show` enum('N','Y') NOT NULL DEFAULT 'Y',
  `residence` int(7) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`,`startdate`),
  KEY `hotelpansion` (`hotelpansion`),
  KEY `nightcount` (`nightcount`),
  KEY `residence` (`residence`),
  KEY `hotelroomtype` (`hotelroomtype`),
  KEY `staytype` (`staytype`)
) ENGINE=MyISAM  DEFAULT CHARSET=cp1251
/*!50100 PARTITION BY RANGE ( MONTH(startdate))
(PARTITION m_01 VALUES LESS THAN (2) ENGINE = MyISAM,
 PARTITION m_02 VALUES LESS THAN (3) ENGINE = MyISAM,
 PARTITION m_03 VALUES LESS THAN (4) ENGINE = MyISAM,
 PARTITION m_04 VALUES LESS THAN (5) ENGINE = MyISAM,
 PARTITION m_05 VALUES LESS THAN (6) ENGINE = MyISAM,
 PARTITION m_06 VALUES LESS THAN (7) ENGINE = MyISAM,
 PARTITION m_07 VALUES LESS THAN (8) ENGINE = MyISAM,
 PARTITION m_08 VALUES LESS THAN (9) ENGINE = MyISAM,
 PARTITION m_09 VALUES LESS THAN (10) ENGINE = MyISAM,
 PARTITION m_10 VALUES LESS THAN (11) ENGINE = MyISAM,
 PARTITION m_11 VALUES LESS THAN (12) ENGINE = MyISAM,
 PARTITION m_12 VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */ AUTO_INCREMENT=133836328 ;

#
# * Fine Tuning
#
key_buffer        = 512M
max_allowed_packet    = 16M
thread_stack        = 192K
thread_cache_size       = 16
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover         = BACKUP
max_connections        = 200
table_cache            = 500
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit    = 8M
query_cache_size        = 256M
tmp_table_size        = 1024M
max_heap_table_size    = 1024M
open_files_limit    = 1400
join_buffer_size    = 32M

SELECT SQL_BUFFER_RESULT it.id, it.id_spo, it.number, DATE_FORMAT(it.startdate, '%d.%m.%Y') AS startdate,
it.nightcount, r1.name AS region1, r2.name AS region2, r1.airp_id AS airport_id, it.staytype as itstaytype, it.hotelpansion as ithotelpansion, it.hotelroomtype as ithotelroomtype,
ht.name AS hotel, htp.name AS stars, it.hotelroomtype as htrid, htr.roomname AS room, it.hotelstop AS ithotelstop, st.name AS staytype, p.name AS pansion, MIN(concat(LPAD(it.price, 5, '0'),it.id)) as price, it.startdate AS itstartdate, hd.description as hotel_descr, ht.id as hotelid
FROM `tez_spo_rows` it
LEFT JOIN it_hotelRooms htr ON (htr.id = it.hotelroomtype) LEFT JOIN it_stayTypes st ON (st.id = it.staytype)
LEFT JOIN it_hotelPansions hp ON (hp.id = it.hotelpansion) LEFT JOIN it_groupTypeAges gt ON (gt.id = it.grouptypeage) LEFT JOIN it_Hotel ht ON (ht.id = htr.hotel) LEFT JOIN it_hotelTypes htp ON (htp.id = ht.hoteltypeid)
LEFT JOIN hotels_description hd ON (hd.id=ht.id) LEFT JOIN it_pansions p ON (p.id = hp.pansion) LEFT JOIN it_regions r1 ON (r1.id = ht.regionid)
LEFT JOIN it_hotelResources hr ON (hr.id = it.residence) LEFT JOIN it_Hotel ht1 ON (ht1.id = hr.hotel) LEFT JOIN it_regions r2 ON (r2.id = ht1.regionid)
WHERE it.show='Y' and it.startdate between '2011-04-27' and '2011-05-02' and
it.nightcount between 2 and 21 and it.staytype='2' and ((r1.id='1285' and r2.id is NULL)) and p.id in(135181, 134164, 5737, 166571, 9004341, 9003550, 9003637, 9002096, 9001916, 30205, 21346, 30204,  5738, 8590, 59695, 60116, 113787, 60107, 30403, 62182, 61632, 32801, 125113, 60040, 60096, 61634, 60090, 48519, 52219, 30523, 115693, 27423, 9003509, 9002803, 177496, 249861, 189732, 9003789, 9001262, 172148, 9003396, 9003460, 9001915, 9005759, 9003648, 172685, 190482, 9001378, 152983, 248750, 145022, 3016698, 203911, 3013774, 3013785, 9002759, 3040106, 173141, 3013611, 9006510, 9006509, 166572, 3023141, 9007750, 3017960, 259265, 3014645, 9007655, 186620, 3016327, 188788, 269549, 7068490, 279543, 259264, 279149)  and ht.hoteltypeid in (2570) and gt.bigchildcount='0' and gt.smallchildcount='0' and gt.adultcount='2' and it.hotelstop='N'
GROUP BY ht.id, htr.id, it.startdate, it.nightcount, it.hotelpansion, it.sporegionset, it.residence ORDER BY price ASC limit 0, 100;