Оптимизация ORDER BY — о чем многие забывают

Пытался, но он закрыт несколько лет назад, по этому и ссылку на него не даю.

разжуйте мне пожалуйста как впредь указывать таблицы в FROM?
я не уловил связь между SELECT и порядком таблиц в FROM

плохо
SELECT t.*, p.*, u.username from phpbb3_topics as t, phpbb3_posts as p, phpbb3_users as u… ORDER by post_id desc

ВЫБИРАЕМ t* p* u* ИЗ t p u — прямой порядок

хорошо
SELECT STRAIGHT_JOIN t.*, p.*, u.username from phpbb3_posts as p, phpbb3_topics as t, phpbb3_users as u… ORDER by post_id desc

ВЫБИРАЕМ t* p* u* ИЗ p t u

ИЛИ всё дело в волшебных пузырьках STRAIGHT_JOIN??
вывод плиз разжуйте

>На php.ru этот запрос используется Яндексом для индексации форума
насмешил, так насмешил…

Во-первых, запросы в таком виде (не отформатированные) очень сложно читать. Почему-то очень многие забывают что SQL запросы нужно также форматирвоать как и код.

Во-вторых, нужно осознавать, что STRAIGHT_JOIN это не серебрянная пуля, а хак. При котором, мы указывает MySQL оптимизатору в каком именно порядке нам нужно клеить таблицы. И если это работает сейчас, это совсем не означает, что это будет и дальше работать с приемлемой для нас скоростью.

Совсем недавно сталкивался с ситуацией, когда вот таким образом заставили определенные запросы на тестовом датасете работать быстро. А когда данные изменились запрос стал очень тормозить, т.к. для MySQL стало «невыгодно» фильровать и сортировать по первой таблице (датасет стал очень большим), и более выгодно было выполнить вначале фильтр по второй таблице в склейки, там где условия фильтров давали небольшой результирующий датасет, и уже клеить первую таблицу к нему.

>> кеш мускуля сбросил перезагрузкой
RESET QUERY CACHE;

Еще GROUP BY… ORDER BY NULL (не сортировать совсем, иначе сортирует по группам), помнится, помогает иногда.

>запросы в таком виде (не отформатированные) очень сложно читать
Понял, учту

>А когда данные изменились запрос стал очень тормозить
А что именно изменилось в данных? Ну чтобы знать…

Секундочку, то есть оптимизатор MySQL никак не принимает во внимание сортировку по ORDER BY? Или просто иногда ошибается, и тогда его можно вот так в нужную сторону пнуть?

Спасибо, полезный нюанс

еще бы синтаксис SQL подсветить

Спасибо за подсказку, исправил :)

Мы недавно писали веб-сервис для экспорта корпоративных данных, и пришли к похожему выводу: один из секретов хорошей производительности подобных запросов — не сортировать вообще. Причём, во многих случаях не сортировать результат — это вполне нормально.

Дополнительный плюс отсутствия сортировки — это то, что стриминг результата из БД начинается практически мгновенно после начала запроса, и его точно так же можно начинать стримить клиенту (браузеру, или через веб-сервис), не дожидаясь, пока весь результат сначала выберется в базе данных и отсортируется.

Возможно, использование STRAIGHT_JOIN и поможет в ряде ситуаций. До этой статьи не знал об этой директиве и пользовался только index hinting'ом. Так что, спасибо вам :)

Помнится, оптимизируя запросы на Firebird тоже натолкнулся на проблему в ORDER BY, но там дело было в том что индекс был по возрастанию (ASC) а сортировка по убыванию (DESC). В конце концов пришлось два индекса создавать. MySQL такими вещами не страдает?

Кстати, а кто из гуру MySQL может сказать, каким образом он «знает» количество записей в полученной выборке без полного её формирования? Помню запрос SELECT COUNT… на большой таблице Firebird мог работать довольно долго, т.к. как сервер вынужден был пройти всю выборку чтобы посчитать кол-во строк. Столкнувшись с этим пришлось переписать десятки запросов, которые использовали COUNT()>0 только чтобы выяснить пустой ли подзапрос или нет. Эту расточительную операцию заменили на EXISTS()

Автору поста вопрос: версия MySQL?

Также неплохо было бы выложить SHOW CREATE TABLE на t, p и u.

Mysql: 5.1

CREATE TABLE `phpbb3_topics` (
 `topic_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
 `forum_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
 `icon_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
 `topic_attachment` tinyint(1) unsigned NOT NULL DEFAULT '0',
 `topic_approved` tinyint(1) unsigned NOT NULL DEFAULT '1',
 `topic_reported` tinyint(1) unsigned NOT NULL DEFAULT '0',
 `topic_title` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
 `topic_poster` mediumint(8) unsigned NOT NULL DEFAULT '0',
 `topic_time` int(11) unsigned NOT NULL DEFAULT '0',
 `topic_time_limit` int(11) unsigned NOT NULL DEFAULT '0',
 `topic_views` mediumint(8) unsigned NOT NULL DEFAULT '0',
 `topic_replies` mediumint(8) unsigned NOT NULL DEFAULT '0',
 `topic_replies_real` mediumint(8) unsigned NOT NULL DEFAULT '0',
 `topic_status` tinyint(3) NOT NULL DEFAULT '0',
 `topic_type` tinyint(3) NOT NULL DEFAULT '0',
 `topic_first_post_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
 `topic_first_poster_name` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
 `topic_first_poster_colour` varchar(6) COLLATE utf8_bin NOT NULL DEFAULT '',
 `topic_last_post_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
 `topic_last_poster_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
 `topic_last_poster_name` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
 `topic_last_poster_colour` varchar(6) COLLATE utf8_bin NOT NULL DEFAULT '',
 `topic_last_post_subject` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
 `topic_last_post_time` int(11) unsigned NOT NULL DEFAULT '0',
 `topic_last_view_time` int(11) unsigned NOT NULL DEFAULT '0',
 `topic_moved_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
 `topic_bumped` tinyint(1) unsigned NOT NULL DEFAULT '0',
 `topic_bumper` mediumint(8) unsigned NOT NULL DEFAULT '0',
 `poll_title` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
 `poll_start` int(11) unsigned NOT NULL DEFAULT '0',
 `poll_length` int(11) unsigned NOT NULL DEFAULT '0',
 `poll_max_options` tinyint(4) NOT NULL DEFAULT '1',
 `poll_last_vote` int(11) unsigned NOT NULL DEFAULT '0',
 `poll_vote_change` tinyint(1) unsigned NOT NULL DEFAULT '0',
 PRIMARY KEY (`topic_id`),
 KEY `forum_id` (`forum_id`),
 KEY `forum_id_type` (`forum_id`,`topic_type`),
 KEY `last_post_time` (`topic_last_post_time`),
 KEY `topic_approved` (`topic_approved`),
 KEY `forum_appr_last` (`forum_id`,`topic_approved`,`topic_last_post_id`),
 KEY `fid_time_moved` (`forum_id`,`topic_last_post_time`,`topic_moved_id`)
) ENGINE=MyISAM AUTO_INCREMENT=37522 DEFAULT CHARSET=utf8 COLLATE=utf8_bin


CREATE TABLE `phpbb3_posts` (
 `post_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
 `topic_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
 `forum_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
 `poster_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
 `icon_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
 `poster_ip` varchar(40) COLLATE utf8_bin NOT NULL DEFAULT '',
 `post_time` int(11) unsigned NOT NULL DEFAULT '0',
 `post_created` int(11) NOT NULL DEFAULT '0',
 `post_approved` tinyint(1) unsigned NOT NULL DEFAULT '1',
 `post_reported` tinyint(1) unsigned NOT NULL DEFAULT '0',
 `enable_bbcode` tinyint(1) unsigned NOT NULL DEFAULT '1',
 `enable_smilies` tinyint(1) unsigned NOT NULL DEFAULT '1',
 `enable_magic_url` tinyint(1) unsigned NOT NULL DEFAULT '1',
 `enable_sig` tinyint(1) unsigned NOT NULL DEFAULT '1',
 `post_username` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
 `post_subject` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
 `post_text` mediumtext COLLATE utf8_bin NOT NULL,
 `post_checksum` varchar(32) COLLATE utf8_bin NOT NULL DEFAULT '',
 `post_attachment` tinyint(1) unsigned NOT NULL DEFAULT '0',
 `bbcode_bitfield` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
 `bbcode_uid` varchar(8) COLLATE utf8_bin NOT NULL DEFAULT '',
 `post_postcount` tinyint(1) unsigned NOT NULL DEFAULT '1',
 `post_edit_time` int(11) unsigned NOT NULL DEFAULT '0',
 `post_edit_reason` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
 `post_edit_user` mediumint(8) unsigned NOT NULL DEFAULT '0',
 `post_edit_count` smallint(4) unsigned NOT NULL DEFAULT '0',
 `post_edit_locked` tinyint(1) unsigned NOT NULL DEFAULT '0',
 PRIMARY KEY (`post_id`),
 KEY `forum_id` (`forum_id`),
 KEY `topic_id` (`topic_id`),
 KEY `poster_ip` (`poster_ip`),
 KEY `poster_id` (`poster_id`),
 KEY `post_approved` (`post_approved`),
 KEY `post_username` (`post_username`),
 KEY `tid_post_time` (`topic_id`,`post_time`)
) ENGINE=InnoDB AUTO_INCREMENT=306720 DEFAULT CHARSET=utf8 COLLATE=utf8_bin


CREATE TABLE `phpbb3_users` (
 `user_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
 `user_type` tinyint(2) NOT NULL DEFAULT '0',
 `group_id` mediumint(8) unsigned NOT NULL DEFAULT '3',
 `user_permissions` mediumtext COLLATE utf8_bin NOT NULL,
 `user_perm_from` mediumint(8) unsigned NOT NULL DEFAULT '0',
 `user_ip` varchar(40) COLLATE utf8_bin NOT NULL DEFAULT '',
 `user_regdate` int(11) unsigned NOT NULL DEFAULT '0',
 `username` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
 `username_clean` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
 `user_password` varchar(40) COLLATE utf8_bin NOT NULL DEFAULT '',
 `user_passchg` int(11) unsigned NOT NULL DEFAULT '0',
 `user_pass_convert` tinyint(1) unsigned NOT NULL DEFAULT '0',
 `user_email` varchar(100) COLLATE utf8_bin NOT NULL DEFAULT '',
 `user_email_hash` bigint(20) NOT NULL DEFAULT '0',
 `user_birthday` varchar(10) COLLATE utf8_bin NOT NULL DEFAULT '',
 `user_lastvisit` int(11) unsigned NOT NULL DEFAULT '0',
 `user_lastmark` int(11) unsigned NOT NULL DEFAULT '0',
 `user_lastpost_time` int(11) unsigned NOT NULL DEFAULT '0',
 `user_lastpage` varchar(200) COLLATE utf8_bin NOT NULL DEFAULT '',
 `user_last_confirm_key` varchar(10) COLLATE utf8_bin NOT NULL DEFAULT '',
 `user_last_search` int(11) unsigned NOT NULL DEFAULT '0',
 `user_warnings` tinyint(4) NOT NULL DEFAULT '0',
 `user_last_warning` int(11) unsigned NOT NULL DEFAULT '0',
 `user_login_attempts` tinyint(4) NOT NULL DEFAULT '0',
 `user_inactive_reason` tinyint(2) NOT NULL DEFAULT '0',
 `user_inactive_time` int(11) unsigned NOT NULL DEFAULT '0',
 `user_posts` mediumint(8) unsigned NOT NULL DEFAULT '0',
 `user_lang` varchar(30) COLLATE utf8_bin NOT NULL DEFAULT '',
 `user_timezone` decimal(5,2) NOT NULL DEFAULT '0.00',
 `user_dst` tinyint(1) unsigned NOT NULL DEFAULT '0',
 `user_dateformat` varchar(30) COLLATE utf8_bin NOT NULL DEFAULT 'd M Y H:i',
 `user_style` mediumint(8) unsigned NOT NULL DEFAULT '0',
 `user_rank` mediumint(8) unsigned NOT NULL DEFAULT '0',
 `user_colour` varchar(6) COLLATE utf8_bin NOT NULL DEFAULT '',
 `user_new_privmsg` int(4) NOT NULL DEFAULT '0',
 `user_unread_privmsg` int(4) NOT NULL DEFAULT '0',
 `user_last_privmsg` int(11) unsigned NOT NULL DEFAULT '0',
 `user_message_rules` tinyint(1) unsigned NOT NULL DEFAULT '0',
 `user_full_folder` int(11) NOT NULL DEFAULT '-3',
 `user_emailtime` int(11) unsigned NOT NULL DEFAULT '0',
 `user_topic_show_days` smallint(4) unsigned NOT NULL DEFAULT '0',
 `user_topic_sortby_type` varchar(1) COLLATE utf8_bin NOT NULL DEFAULT 't',
 `user_topic_sortby_dir` varchar(1) COLLATE utf8_bin NOT NULL DEFAULT 'd',
 `user_post_show_days` smallint(4) unsigned NOT NULL DEFAULT '0',
 `user_post_sortby_type` varchar(1) COLLATE utf8_bin NOT NULL DEFAULT 't',
 `user_post_sortby_dir` varchar(1) COLLATE utf8_bin NOT NULL DEFAULT 'a',
 `user_notify` tinyint(1) unsigned NOT NULL DEFAULT '0',
 `user_notify_pm` tinyint(1) unsigned NOT NULL DEFAULT '1',
 `user_notify_type` tinyint(4) NOT NULL DEFAULT '0',
 `user_allow_pm` tinyint(1) unsigned NOT NULL DEFAULT '1',
 `user_allow_viewonline` tinyint(1) unsigned NOT NULL DEFAULT '1',
 `user_allow_viewemail` tinyint(1) unsigned NOT NULL DEFAULT '1',
 `user_allow_massemail` tinyint(1) unsigned NOT NULL DEFAULT '1',
 `user_options` int(11) unsigned NOT NULL DEFAULT '230271',
 `user_avatar` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
 `user_avatar_type` tinyint(2) NOT NULL DEFAULT '0',
 `user_avatar_width` smallint(4) unsigned NOT NULL DEFAULT '0',
 `user_avatar_height` smallint(4) unsigned NOT NULL DEFAULT '0',
 `user_sig` mediumtext COLLATE utf8_bin NOT NULL,
 `user_sig_bbcode_uid` varchar(8) COLLATE utf8_bin NOT NULL DEFAULT '',
 `user_sig_bbcode_bitfield` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
 `user_from` varchar(100) COLLATE utf8_bin NOT NULL DEFAULT '',
 `user_icq` varchar(15) COLLATE utf8_bin NOT NULL DEFAULT '',
 `user_aim` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
 `user_yim` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
 `user_msnm` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
 `user_jabber` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
 `user_website` varchar(200) COLLATE utf8_bin NOT NULL DEFAULT '',
 `user_occ` text COLLATE utf8_bin NOT NULL,
 `user_interests` text COLLATE utf8_bin NOT NULL,
 `user_actkey` varchar(32) COLLATE utf8_bin NOT NULL DEFAULT '',
 `user_newpasswd` varchar(40) COLLATE utf8_bin NOT NULL DEFAULT '',
 `user_form_salt` varchar(32) COLLATE utf8_bin NOT NULL DEFAULT '',
 `user_new` tinyint(1) unsigned NOT NULL DEFAULT '1',
 `user_reminded` tinyint(4) NOT NULL DEFAULT '0',
 `user_reminded_time` int(11) unsigned NOT NULL DEFAULT '0',
 PRIMARY KEY (`user_id`),
 UNIQUE KEY `username_clean` (`username_clean`),
 KEY `user_birthday` (`user_birthday`),
 KEY `user_email_hash` (`user_email_hash`),
 KEY `user_type` (`user_type`)
) ENGINE=MyISAM AUTO_INCREMENT=35337 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

— стандартная база форума phpbb3

Вместо сброса кеша удобно использовать SELECT SQL_NO_CACHE ...

Для 100% уверенности в полученном результате я решил мускуль рестартнуть — он же грузит в память таблицы, к которым были обращения и т.п.

Да, тоже столкнулся с проблемой, что нужно из одной таблицы отсортированные данные. но условия нужно чекать по другим и случается жесть. Думал, уже, какой-то хитрый кэш прикручивать, чтоб из одной таблицы можно было и условия указать и сортировку сделать, но STRAIGHT_JOIN хорошо помог :)
Спасибо за информацию.

За SELECT t.*,… — уже сразу нужно отбивать руки.

Был у меня случай из практики: обратился ко мне один человек, спросил — что-то не так с их системой «нипель», когда была написана — работала идеально, а потом со временем, выборка/правка/удаление идет с задержкой до минуты.
Я посмотрел на базу и понял, в чем подвох: в некоторых таблицах были блобы от 50 до 200 МБ (какие-то картинки и видео к продукции). Сорцов к их системе не было, программа была написана на делфи.
Добавив в конфиг мускуля дебаг запросов — я увидел, что там как раз SELECT * FROM…