Оптимизиция запроса select ... where ... order by price limit 200 из бд в 18 000 000 записей

[lexey2006.90]

Я добавил вот такие индексы :
alter table gate_spo_price_egipt add index price1 (depature_date, price);
alter table gate_spo_price_egipt add index price2 (room, depature_date, price);
alter table gate_spo_price_egipt add index price3 (days, room, depature_date, price);
alter table gate_spo_price_egipt add index price4 (status, days, room, depature_date, price);
alter table gate_spo_price_egipt add index price5 (operator, status, days, room, depature_date, price);
alter table gate_spo_price_egipt add index price6 (chld, operator, status, days, room, depature_date, price);

Щас с помощью explain посмотрю какой индекс он выберет на достаточно сложном запросе. Все равно не очень представляю каким должен быть индекс. (ведь если посмотреть сочетания полей - то их получится 6^6 - т.к. возможно выбрать 6 полей)

 

[lexey2006.90]

Просто сделайте правильные ключи для этого запроса:
PRIMARY KEY (`depature_date`,`id`) -- если используете партицирование по дате
key (`depature_date`,`room`)
Можно добавить:
key (price)
но честно говоря, я не помню будет ли оптимизатор в этом запросе использовать его для сортировки или нет - попытайте.

Щас попытаю как вы сказали)

 

[lexey2006.90]

Так как же мне сравнивать запросы с разными ключами, если разное время выполнения запросов все время?

 

[lexey2006.90]

id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------+-------+---------------+------------+---------+------+-------+-------------+
| 1 | SIMPLE | gate_spo_price | index | seach_gate | seach_sort | 28 | NULL | 13481 | Using where |

Оптимизатор ответил вот так: т.е. выбрал ключ, который был до этого создан программистом, т.е. все эти 6 ключей не подошли)

Запрос:
explain select * from gate_spo_price where room = '23' AND depature_date >= '2011-06-13' AND depature_date <= '2011-06-17' AND (operator='37410' or operator='778291' or operator='785389' or operator='785891') AND status=0 AND chld=0 ORDER BY price ASC limit 100;
Ключ seach_sort:
price 3956738 A
depature_date 3956738 A
room 3956738 A
days 3956738 A
status 3956738 A
operator 3956738 A

А сделайте шесть индексов по каждому полю (одно поле на индекс).
Попытайте, а потом расскажите нам, как оптимизатор их объединил. И какова скорость была при этом, - лучше/хуже.

Щас сделаю)

 

[lexey2006.90]

Первый игнорируйте. Время берите по второму/третьему разу (подряд, конечно).

Спасибо, буду знать) При партицировании есть разница только при первом запросе)

 

[lexey2006.90]

Созданные индексы:
alter table gate_spo_price_egipt add index price1 (depature_date);
alter table gate_spo_price_egipt add index price2 (price);
alter table gate_spo_price_egipt add index price3 (days);
alter table gate_spo_price_egipt add index price4 (room);
alter table gate_spo_price_egipt add index price5 (operator);
alter table gate_spo_price_egipt add index price6 (chld);
alter table gate_spo_price_egipt add index price6 (status);
alter table gate_spo_price_egipt add index price6 (city);

Запрос:
explain select * from gate_spo_price_egipt where room = '23' AND depature_date >= '2011-06-13' AND depature_date <= '2011-06-17' AND (operator='37410' or operator='778291' or operator='785389' or operator='785891') AND status=0 AND chld=0 ORDER BY price ASC limit 100;

Ответ оптимизатора:
id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------------+-------+-----------------------------------------------+------------+---------+------+------+-------------+
| 1 | SIMPLE | gate_spo_price_egipt | index | seach_gate,price1,price4,price5,price6,price7 | seach_sort | 20 | NULL | 7840 | Using where |

Опять выбран ключ seach_sort;

 

[lexey2006.90]

Запрос выполнялся 47 секунд на 4 000 000 записей)

 

[lexey2006.90]

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------------+-------+------------------------------------+--------+---------+------+-------+-------------+
| 1 | SIMPLE | gate_spo_price_egipt | index | price1,price4,price5,price6,price7 | price2 | 4 | NULL | 10339 | Using where |

Удалил все ключи, оставил только 6 из них)

 

[lexey2006.90]

Какая у Вас версия движка?

5.5.13

 

[lexey2006.90]

CREATE TABLE IF NOT EXISTS `gate_spo_price_egipt_new` (
`id` bigint(16) NOT NULL AUTO_INCREMENT,
`date_from` date NOT NULL,
`date_to` date NOT NULL,
`country` int(11) NOT NULL,
`city` int(11) NOT NULL,
`hotel` int(11) NOT NULL,
`operator` int(11) NOT NULL,
`star` int(11) NOT NULL,
`room` int(11) NOT NULL,
`pansion` int(11) NOT NULL,
`depature_from` int(11) NOT NULL,
`depature_to` int(11) NOT NULL,
`depature_date` date NOT NULL,
`days` int(11) NOT NULL,
`price` int(11) NOT NULL,
`status` tinyint(4) NOT NULL,
`chld` int(11) NOT NULL,
`age1` int(11) NOT NULL,
`age2` int(11) NOT NULL,
`age3` int(11) NOT NULL,
`age4` int(11) NOT NULL,
PRIMARY KEY (`depature_date`,`id`),
KEY `price1` (`depature_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
/*!50100 PARTITION BY RANGE (DAYOFYEAR(depature_date))
(PARTITION p0 VALUES LESS THAN (152) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (158) ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN (164) ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN (170) ENGINE = InnoDB,
PARTITION p4 VALUES LESS THAN (176) ENGINE = InnoDB,
PARTITION p5 VALUES LESS THAN (181) ENGINE = InnoDB,
PARTITION p6 VALUES LESS THAN (187) ENGINE = InnoDB,
PARTITION p7 VALUES LESS THAN (194) ENGINE = InnoDB,
PARTITION p8 VALUES LESS THAN (201) ENGINE = InnoDB,
PARTITION p9 VALUES LESS THAN (208) ENGINE = InnoDB,
PARTITION p10 VALUES LESS THAN (215) ENGINE = InnoDB,
PARTITION p11 VALUES LESS THAN (222) ENGINE = InnoDB,
PARTITION p12 VALUES LESS THAN (229) ENGINE = InnoDB,
PARTITION p13 VALUES LESS THAN (236) ENGINE = InnoDB,
PARTITION p14 VALUES LESS THAN (245) ENGINE = InnoDB,
PARTITION p15 VALUES LESS THAN (252) ENGINE = InnoDB,
PARTITION p16 VALUES LESS THAN (259) ENGINE = InnoDB,
PARTITION p17 VALUES LESS THAN (266) ENGINE = InnoDB,
PARTITION p18 VALUES LESS THAN (273) ENGINE = InnoDB,
PARTITION p19 VALUES LESS THAN (280) ENGINE = InnoDB,
PARTITION p20 VALUES LESS THAN (287) ENGINE = InnoDB,
PARTITION p21 VALUES LESS THAN (294) ENGINE = InnoDB,
PARTITION p22 VALUES LESS THAN (303) ENGINE = InnoDB,
PARTITION p23 VALUES LESS THAN (310) ENGINE = InnoDB,
PARTITION p24 VALUES LESS THAN (317) ENGINE = InnoDB,
PARTITION p25 VALUES LESS THAN (324) ENGINE = InnoDB,
PARTITION p26 VALUES LESS THAN (329) ENGINE = InnoDB,
PARTITION p27 VALUES LESS THAN (336) ENGINE = InnoDB,
PARTITION p28 VALUES LESS THAN (343) ENGINE = InnoDB,
PARTITION p29 VALUES LESS THAN (350) ENGINE = InnoDB,
PARTITION p30 VALUES LESS THAN (357) ENGINE = InnoDB,
PARTITION p31 VALUES LESS THAN (364) ENGINE = InnoDB,
PARTITION p32 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */

Ругается на это: PRIMARY KEY (`depature_date`,`id`),
Ругается на это: PRIMARY KEY (`id`),
Не ругается : PRIMARY KEY (`id`, `depature_date`),

 

[lexey2006.90]

select * from (select * from gate_spo_price where room = '23' AND depature_date >= '2011-06-10' AND depature_date <= '2011-06-30' AND status=0 AND chld=0) as t ORDER BY price ASC limit 200;

Такой запрос из 18 000 000 записей выполняется 25 сек) - ненормально.

 

[lexey2006.90]

Даже интересно стало, кто кого дурит. Если хотите, могу поиграться малость на своём локальном компе, если дадите дамп, хотя бы на пару миллионов записей (можно и все). Но не сегодня (сегодня я занят), - завтра, если не к спеху.

да, конечно дам. но правда он весит 400 Мб. Куда лучше залить? На торрент нет доступа. - закрыто админом. Задача рабочая, из-за нее не работает нормально сервер, и, даже, иногда ложится nginx. База gate_32, таблица gate_spo_price. К слову, я разделил ее уже на 3 части) Щас попробую сделать только дамп этой таблицы.

Дурит, не должен он price2 (price) выбирать для этого запроса. Хотя ... "жираф большой, ему видней"

Что мне еще можно попробовать сделать? Оставить эти индексы и поиграться с ними? По умолчанию в запросе select * from gate_spo_price он сам выбирает какой индекс использовать, или он не использует никакой, если ему не указать?

 

[lexey2006.90]

Дамп лежит у меня на сервере - щас с него скину на piclya.com/tmp - это ftp сервер

 

[lexey2006.90]

Щас сначала на рапиду попытаюсь кинуть) Вроде скорость закачки нормальная)

Скажите пожалуйста, а Вы Администратор Баз Данных?

 

[lexey2006.90]

А что?

Стало интересно.

ftp://piklya.com/tmp/ - файл all.2011-06-10.sql.gz (можно через браузер - можно через проводник)

 

[lexey2006.90]

Может быть обменяемся контактом skype?

 

[lexey2006.90]

Просто у меня заблокирована аська на работе, может мейл агент?

 

[Gas]

погоди, не удаляй пока дамп, щас тоже стяну и если будет время поиграюсь.

 

[lexey2006.90]

Давайте тогда скайп - мой ник lashnag. Я щас в онлайн)

 

[lexey2006.90]

Нашел очень странный запрос:
select * from gate_spo_price USE INDEX (seach_sort) where country = '315' AND room = '23' AND depature_date >= '2011-06-27' AND depature_date <= '2011-07-3' AND status=0 AND chld=0 ORDER BY price ASC limit 100; - вешает запрос
select * from gate_spo_price USE INDEX (seach_sort) where country = '315' AND room = '23' AND depature_date >= '2011-06-27' AND depature_date <= '2011-07-30' AND status=0 AND chld=0 ORDER BY price ASC limit 100; - выполняется быстро
select * from gate_spo_price USE INDEX (seach_sort) where country = '315' AND room = '23' AND depature_date >= '2011-06-27' AND depature_date <= '2011-07-4' AND status=0 AND chld=0 ORDER BY price ASC limit 100; - тоже быстро - в чем причина?