Помогите, плиз.
Структура:
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 ;Таблица содержит 100 млн. записей и весит с индексами 11Гб. Таблица сегментирована по месяцам, но особого прироста в скорости от этого я не заметил. Не могу даже проверить, работает это или нет. В show processlist почти всегда висит Copying to tmp table. Процентов 90 времени. Получается, что индексы работают, выбирает быстро, но копирует во временную таблицу очень долго. tmp_table_size выкручивал до 5 гигабайт - толку никакого. Сервер физический, с местом проблем нет. Памяти 8 Гб, проц 4 ядра какого-то ксеона.
Конфиг my.cnf.
#
# * 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
Сделал временную директорию tmpfs и смонтировал её на /tmp/mysqltmp как описано в статье: http://www.gnuman.ru/stuff/mysql_and_tmpfs/ говорят помогает. Что еще можно сделать? В идеале нужно свести к минимуму этот Copying to tmp table. Так как это все преобретает лавинный характер. Когда один-два человека выбирают, запрос длится менее минуты и человек дожидается результатов. Когда же в это время еще народ заходит - запросы виснут намертво.
Запрос:
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;
Explain запроса в скриншоте.
