|
Задавайте вопросы, мы ответим
Вы не зашли.
Дано: 2 таблицы - towns(id, title), vacancies(id, town_id).
Надо получить: список названий всех городов из towns с количеством вакансий в этом городе.
Следующий запрос выбирает только города, для которых есть записи в vacancies.
SELECT towns.title, COUNT(vacancies.id) FROM towns, vacancies WHERE towns.id = vacancies.town_id GROUP BY towns.title
Подскажите, пожалуйста, как получить ВСЕ города? Заранее очень признателен.
Неактивен
используйте left join
http://dev.mysql.com/doc/refman/5.1/en/join.html
Неактивен
Vasya, был у меня вариант с left join, не устроила скорость работы (10000 записей за 1,5 сек)
Неактивен
Да, группировать по имени города — идея ужасная. Но подзапрос
в данном случае тоже лишний.
Неактивен
paulus, каким именно на ваш взгяд должен быть запрос в данной ситуации?
Неактивен
Подойдет тот, что у Вас написан, но GROUP BY towns.id.
Нужны ключики на vacancies (town_id, id) и towns (id).
Неактивен
paulus, насколько я Вас понял запрос должен выглядеть так:
Неактивен
А EXPLAIN от обоих вариантов покажите?
Неактивен
Количество записей:
vacancies 14406
towns 25
Сгенерил новый тестовый набор записей для vacancies(более равномерный). Теперь примерно по 600 записей на каждый город (раньше все записи приходились на 5 городов).
Индексы towns: id(PRIMARY).
Индексы vacancies: id(PRIMARY), tonw_id(INDEX).
Время выполнения:
без вложенного запроса 0,035 сек.
с вложенным запросом 0,007 сек.
Результаты EXPLAIN в прикрепленном файле.
Отредактированно mihim (27.09.2010 11:55:21)
Неактивен
А теперь посмотрите, какой индекс я предложил сделать на vacancies 
И, если после добавления, не будет лопать индекс целиком, я бы
добавил ORDER BY vacancies.town_id.
Неактивен
paulus, насчет индексов сначала не так Вас понял. Сделал индекс по двум полям (vacancies.town_id, vacancies.id). Время выполнения уменьшилось с 0,035 сек до 0,02 сек. ORDER BY vacancies.town_id на ситуацию никак не повлиял.
При этом вариант с вложенным запросом все равно работает в 3 раза быстрее (0,007 сек).
Отредактированно mihim (27.09.2010 14:18:01)
Неактивен
Не повлиял — это Вы судите по EXPLAIN?
Неактивен
И по EXPLAIN (картинка прицеплена) и по скорости работы.
Неактивен
И правда, он странно выбирает 
Неактивен
да все нормально работает.
в первом запросе из-за того что count и group by оперируют данными из разных таблиц и появляется using temporary.. и using filesort.
во втором запросе те самые using temporary и filesort появляются из-за требования отсортировать выборку по vacancies.town_id. на мой взгляд лишнее требование, т.к. group by по умолчанию обеспечит сортировку
Неактивен
Спасибо за помощь всем откликнувшимся!
И все же кто-нибудь может объяснить почему вариант с вложенным запросом, имея далеко неидеальный EXPLAIN, работает в 3 раза быстрее обычного запроса с оптимизированным EXPLAIN?
Неактивен
Неактивен
Ррр, ну почему приходится делать тестовые данные, чтобы показать, что
на самом деле всё хорошо?
mysql> SHOW CREATE TABLE towns\G
*************************** 1. row ***************************
Table: towns
Create Table: CREATE TABLE `towns` (
`id` int(11) DEFAULT NULL,
`title` varchar(20) DEFAULT NULL,
KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> SHOW CREATE TABLE vacancies\G
*************************** 1. row ***************************
Table: vacancies
Create Table: CREATE TABLE `vacancies` (
`id` int(11) DEFAULT NULL,
`town_id` int(11) DEFAULT NULL,
KEY `town_id` (`town_id`,`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> SELECT * FROM towns;
+------+-------------------------------+
| id | title |
+------+-------------------------------+
| 1 | Москва |
| 2 | Санкт-Петербург |
| 3 | Владимир |
| 4 | Новгород |
+------+-------------------------------+
4 rows in set (0.00 sec)
mysql> SELECT * FROM vacancies;
+------+---------+
| id | town_id |
+------+---------+
| 1 | 1 |
| 2 | 1 |
| 3 | 2 |
| 4 | 3 |
+------+---------+
4 rows in set (0.00 sec)
mysql> EXPLAIN SELECT title, COUNT(v.id) FROM towns t LEFT JOIN vacancies v ON t.id = v.town_id GROUP BY t.id ORDER BY NULL;
+----+-------------+-------+------+---------------+---------+---------+-----------+------+-----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+---------+---------+-----------+------+-----------------+
| 1 | SIMPLE | t | ALL | NULL | NULL | NULL | NULL | 4 | Using temporary |
| 1 | SIMPLE | v | ref | town_id | town_id | 5 | test.t.id | 1 | Using index |
+----+-------------+-------+------+---------------+---------+---------+-----------+------+-----------------+
2 rows in set (0.00 sec)
mysql> SELECT title, COUNT(v.id) FROM towns t LEFT JOIN vacancies v ON t.id = v.town_id GROUP BY t.id ORDER BY NULL;
+-------------------------------+-------------+
| title | COUNT(v.id) |
+-------------------------------+-------------+
| Москва | 2 |
| Санкт-Петербург | 1 |
| Владимир | 1 |
| Новгород | 0 |
+-------------------------------+-------------+
4 rows in set (0.00 sec)Неактивен
а как же using temporary...? вообще говоря это не очень хорошо.
Неактивен
попробуйте загнать в vacancies сильно больше данных(сотни тысяч, миллионы) и "Using temporary" обязательно окажет свое пагубное влияние на время исполнения
Неактивен
Попробуйте загнать сотни тысяч данных в выборку без WHERE, и Вы поймете,
почему Ваше требование не важно в данном случае
Но раз уж Вы придираетесь, то вот так Вас должно устроить:
mysql> ALTER TABLE towns DROP KEY id, ADD PRIMARY KEY(id); Query OK, 4 rows affected (0.38 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> EXPLAIN SELECT title, COUNT(v.id) FROM towns t LEFT JOIN vacancies v ON v.town_id = t.id GROUP BY t.id ORDER BY NULL; +----+-------------+-------+-------+---------------+---------+---------+-----------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+-----------+------+-------------+ | 1 | SIMPLE | t | index | NULL | PRIMARY | 4 | NULL | 4 | | | 1 | SIMPLE | v | ref | town_id | town_id | 5 | test.t.id | 1 | Using index | +----+-------------+-------+-------+---------------+---------+---------+-----------+------+-------------+ 2 rows in set (0.00 sec)
Неактивен
да нет, так тоже не особо весело. я бы даже сказал совсем не весело. подзапрос качественно лучше.
Неактивен
Вы правда в это верите? 
Но если Вы автор решения с подзапросом — то, разумеется, лучше: это
решение было получено куда быстрее
Неактивен
paulus, в EXPLAIN все красиво, но хоть убейте меня, скорость работы варианта с подзапросом выше в 3 раза. На 130 000 записей в vacancies и 25 городах - 0,07 против 0,19 сек В теории с подзапросом должно работать медленнее, но на практике всё иначе...
Отредактированно mihim (29.09.2010 22:58:50)
Неактивен
Я ж не против, я же давно уже говорю, что раз рабочее решение есть —
надо использовать его. В теории нигде не сказано, что подзапросы
должны работать медленнее. На практике же это почти всегда так:
MySQL определяет подзапрос зависимым, и выполняет его много раз
вместо того, чтобы сделать это единожды. В Вашем же случае это не так,
поэтому скорости с подзапросом и без должны быть сравнимы. Что мы,
собственно, и получили.
Неактивен
