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

    На тему оптимизации MySQL запросов написано очень много, все знают как оптимизировать SELECT, INSERT, что нужно джоинить по ключу и т.д. и т.п.

    Но есть один момент, тоже неоднократно описанный во всех мануалах, но почему-то про него все забывают.

    Оптимизация ORDER BY в запросах с джоинами.


    Оправдание: поиском воспользовался, не нашел !

    Большинство считают, что если ORDER BY происходит по индексу, то и проблем никаких нет, однако это не всегда так. Недавно я разбирался с одним запросом который дико тормозил базу хотя вроде все индексы на нужных местах. ORDER BY оказался последним местом, куда я ткнулся, и проблема оказалась именно там.

    Маленькая выдержка из мануалов по оптимизации:

    ===
    Как MySQL оптимизирует ORDER BY
    Ниже приведены некоторые случаи, когда MySQLне может использовать индексы, чтобы выполнить ORDER BY

    Связываются несколько таблиц, и столбцы, по которым делается
    сортировка ORDER BY, относятся не только к первой неконстантной
    (const) таблице, используемой для выборки строк(это первая таблица
    в выводе EXPLAIN, в которой не используется константный, const, метод выборки строк).

    ===

    Для ORDER BY важно, чтобы таблица, по которой будет производиться сортировка была на первом месте. Однако по умолчанаю, в каком бы порядке вы не джойнили таблицы, встроенный в mysql оптимизатор переставит их в том порядке, как он сам посчитает нужным. То есть если вы поставили нужную таблицу первой в запросе, то это вовсе не означает, что она будет на самом деле первой.

    К счастью, оптимизатору mysql можно сказать, чтобы он джоинил таблицы в том порядке, какой мы ему указали, для этого нужно в SELECT добавить команду STRAIGHT_JOIN:

    SELECT STRAIGHT_JOIN… FROM table JOIN…… ORDER BY table.row

    Проверка на mysql базе форума PHPBB3 содержащей около 300 000 постов:

    SELECT t.*, p.*, u.username 
    FROM phpbb3_topics as t, phpbb3_posts as p,  phpbb3_users as u 
    WHERE t.topic_replies>0 AND p.poster_id=u.user_id AND topic_first_post_id<>p.post_id AND topic_approved=1 AND p.topic_id=t.topic_id AND t.forum_id='16' AND p.post_id<'244103' 
    ORDER by post_id desc LIMIT 40


    Query took 12.2571 sec

    в explain видим ужасное: Using where; Using temporary; Using filesort

    Меняем порядок таблиц (кеш мускуля сбросил перезагрузкой):

    SELECT STRAIGHT_JOIN t.*, p.*, u.username 
    FROM phpbb3_posts as p, phpbb3_topics as t, phpbb3_users as u 
    WHERE t.topic_replies>0 AND p.poster_id=u.user_id AND topic_first_post_id<>p.post_id AND topic_approved=1 AND p.topic_id=t.topic_id AND t.forum_id='13' AND p.post_id<'234103' 
    ORDER by post_id desc LIMIT 40


    Query took 0.0447 sec

    в explain: Using where;

    Вот такой принудительной перестановкой таблиц мы ускорили выполнение запроса в 300 раз!

    Это совсем не значит, что нужно всегда использовать STRAIGHT_JOIN и самому следить за порядком таблиц. Но в некоторых случах это необходимо.

    P.S. Этот запрос используется Яндексом для индексации форумов phpbb. До оптимизации, яндекс-бот клал сервер php.ru каждую ночь на несколько часов (сервер не очень мощный). В блоге Яндекса была дисскуссия на эту тему, но она закрыта пару лет назад и решение там не озвучено.
    Поделиться публикацией

    Комментарии 65

      –3
      Яндексу то отписался? Что б в исходниках поправили?
        +1
        У них NoSQL на инвертированном индексе. Они ищут документы за log(N), где N около 400 000. Им фиолетово на order by.
          0
          Речь идёт о боте, письмо надо слать в PHPBB
            0
            не, это яндексовский плагин, а блог с его обсуждением закрыт в 10 году
        0
        Пытался, но он закрыт несколько лет назад, по этому и ссылку на него не даю.
          +1
          разжуйте мне пожалуйста как впредь указывать таблицы в 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??
          вывод плиз разжуйте
            0
            > я не уловил связь между SELECT и порядком таблиц в FROM
            её нету :)
            важен порядок таблиц после FROM
              0
              а какой тогда алгоритм? просто переставлять таблицы и засекать изменения?
                +1
                не нужно переставлять таблицы. Нужно смотреть Explain, SHOW STATUS, PROFILING и делать выводы, что в вашем запросе не так. Может какой-то индекс не используется, может какой-то индекс нужно добавить. Может нужно изменить запрос — добавить derived table или другой трюк. Это в любом случае лучше чем FORCE INDEX или STRAIGHT_JOIN
                  +1
                  спасибо. запомню совет.
                  explain использую активно, но задачи у мя скромные и все мои «оптимизации» как учёба делать правильно, хотя острой необходимости нет =/
                  пошёл читать derived table
            +10
            >На php.ru этот запрос используется Яндексом для индексации форума
            насмешил, так насмешил…
              +2
              Подозреваю что яндекс просит список постов для индексации, а код в свою очередь использует этот запрос.
                0
                тогда формулировка должна быть приблизительно такой:
                при формировании файла индексации для поисковиков (его использует на только Яндекс) на форуме phpBB (форум phpBB используется не только на php.ru) выполняется аналогичный запрос (а лучше привести конкретный пример)
                  +1
                  Я оригинал в таком смысле и понял, даже мысли не возникло, что можно понять по другому.
              +7
              Во-первых, запросы в таком виде (не отформатированные) очень сложно читать. Почему-то очень многие забывают что SQL запросы нужно также форматирвоать как и код.

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

              Совсем недавно сталкивался с ситуацией, когда вот таким образом заставили определенные запросы на тестовом датасете работать быстро. А когда данные изменились запрос стал очень тормозить, т.к. для MySQL стало «невыгодно» фильровать и сортировать по первой таблице (датасет стал очень большим), и более выгодно было выполнить вначале фильтр по второй таблице в склейки, там где условия фильтров давали небольшой результирующий датасет, и уже клеить первую таблицу к нему.
                +3
                >> кеш мускуля сбросил перезагрузкой
                RESET QUERY CACHE;
                  +2
                  а почему по не по хардкору просто добавить в SELECT — SQL_NO_CACHE?
                    0
                    Это вы у меня спрашиваете?
                      0
                      Ну вы же предложили сбросить кеш такой конструкцией :-)

                      Когда я читал статью у меня сложилось впечатление, что автор сбросил кеш перезапуском MySQL :-)
                        0
                        Так и есть. Я подсказал как можно сбросить не перезапуском. Почему автор не использовал SQL_NO_CACHE этого я знать не могу )
                  +2
                  Еще GROUP BY… ORDER BY NULL (не сортировать совсем, иначе сортирует по группам), помнится, помогает иногда.
                    0
                    >запросы в таком виде (не отформатированные) очень сложно читать
                    Понял, учту

                    >А когда данные изменились запрос стал очень тормозить
                    А что именно изменилось в данных? Ну чтобы знать…
                      0
                      Ну сами данные изменились, и условия (Where) по которым проходила склейка таблиц.
                      Т.е. вначале получалось, что датасет из первой таблицы, после применения фильтров, был не очень большим (несколько сотен записей), поэтому было быстрей вначале подготовить этот датасет, отсортировать его и проводить INNER JOIN склейку.
                      Когда же этот датасет стал большим (десятки тысяч записей), то выгодней стало вначале подготовить второй датасет (по условию фильтров на второй таблице), и уже клеить к нему первую таблицу. И хоть сортировка в этом случае и была без индекса, но она происходила в памяти на гораздо меньшем числе записей (1-2 порядка).
                      +1
                      Секундочку, то есть оптимизатор MySQL никак не принимает во внимание сортировку по ORDER BY? Или просто иногда ошибается, и тогда его можно вот так в нужную сторону пнуть?
                        0
                        Как это не принимает? Конечно, принимает. Вы это можете заметить, например, в случае если у вас подобный запрос:

                        SELECT blah-blah FROM table WHERE col1 = x ORDER BY col2

                        При условии, что у вас есть два индекса по полям col1 и col2
                        MySQL будет принимать решение какой именно индекс в данном запросе «выгодней» использовать.

                          0
                          В этом случае индекс по col2 никогда не выгоднее. По крайней мере я не могу придумать ни одного сценария.
                            +2
                            Запрос был исключительно для примера, и я склонен с вами согласиться, но все же если пофантазировать, то индекс по col2 может быть выгодней, чем по col1 в случае если Cardinality индекса col1 очень большая. Например это поле цвет.
                              0
                              Кстати да, таки хороший пример.
                              0
                              Такой вот извращённый сценарий
                              WHERE 1=1 OR col1 = x ORDER BY col2
                                0
                                не уверен, что на стадии оптимизации запроса часть 1=1 не будет удалена и таким образом не повлияет на ход выполнения
                                  0
                                  Там OR — так что как раз 1=1 останется :-)
                                    0
                                    не успел отписаться, что я не увидел OR :-)
                                      +1
                                      я просто совсем недавно открыл для себя интересные особенности работы оптимизатора при работе с константными выражениями, поэтому и здесь поспешил отписаться.

                                      А особенности такие:

                                      запрос у которого в where выражение из констант (правая часть выражения) проигрывает запросу, у которого в правой части константа

                                      WHERE
                                      ( e.website_id = '1' ) AND ( e.customer_group_id = 0) AND (e.min_price >= ((3089.24 — 0.005) / 1))

                                      хуже, чем

                                      WHERE
                                      ( e.website_id = '1' ) AND ( e.customer_group_id = 0) AND e.min_price >= 3089.235

                                      зато
                                      такой запрос:

                                      WHERE
                                      (e.min_price >= 1999.995) AND (e.min_price < 2999.995) AND
                                      (e.min_price >= 1999.995) AND (e.min_price < 5999.995) AND
                                      (e.min_price >= 999.995) AND (e.min_price < 10999.995) AND
                                      (e.min_price >= 2694.235) AND (e.min_price < 2999.995);

                                      выполняется по времени аналогично такому

                                      (e.min_price >= 2694.235) AND (e.min_price < 2999.995);
                                        0
                                        В первую особенность не верю :-S
                                          0
                                          и я тоже не верил. Пока не проверил на 5.0.37, 5.1, 5.5.18 версиях
                                          Вторая особенность как раз выглядит логичной, чего не скажешь о первой.
                                          0
                                          первая особенность — только до 5.5.x
                                          Начиная с 5.5 оптимизатор заменит выражение на константу еще до выполнения запроса.
                                            0
                                            Проверял на 5.5.18 результат был тот же
                                              0
                                              если будет sql-скрипт, который это демонстрирует — я посмотрю.

                                              То что 5.5 заменяет выражения на константы — это точно, могу показать где в коде это происходит :)
                                      0
                                      Не совсем понимаю как это относится к обсуждаемому вопросу. `col1 = x` оптимизатор выкинет на этапе разбора запроса.
                                        0
                                        Прошу прощения, конечно я не прав
                                      0
                                      Ну так вот и я о том же. А в статье пишут, будто так надо делать всегда.
                                        0
                                        Нет, что вы… обычно мускуль сам не дурак. Решение довольно узкой направленности.
                                        Подправлю текст, чтобы такой мысли не возникало.
                                          0
                                          это очень плохой стиль. И этого нужно стараться всячески избегать. Более того, при правильно сформированном SQL запросе и актуальной статистике индексов (периодическом выполнении Analyze table, что более актуально для MyISAM и менее для InnoDB). У вас такие проблемы будут возникать достаточно редко.
                                            0
                                            Согласен, исправил в тексте.
                                            Но в моей ситуации — оптимизировать конкретный запрос мне это очень помогло. Ибо структуру БД форума не поменять :)
                                        0
                                        Да, тоже было интересно. Видел не раз, как он порядок таблиц сам расставлял как нужно.
                                        0
                                        Спасибо, полезный нюанс
                                          0
                                          еще бы синтаксис SQL подсветить
                                            0
                                            Спасибо за подсказку, исправил :)
                                              0
                                              Мы недавно писали веб-сервис для экспорта корпоративных данных, и пришли к похожему выводу: один из секретов хорошей производительности подобных запросов — не сортировать вообще. Причём, во многих случаях не сортировать результат — это вполне нормально.

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

                                              Возможно, использование STRAIGHT_JOIN и поможет в ряде ситуаций. До этой статьи не знал об этой директиве и пользовался только index hinting'ом. Так что, спасибо вам :)
                                                0
                                                >>не дожидаясь, пока весь результат сначала выберется в базе данных и отсортируется.
                                                вообще-то при сортировке по индексированному полю СУБД как раз таки не вынуждена формировать всю выборку чтобы отсортировать
                                                  0
                                                  Поэтому я и написал: «Возможно, использование STRAIGHT_JOIN и поможет в ряде ситуаций». Но спасибо за уточнение!
                                                0
                                                Помнится, оптимизируя запросы на Firebird тоже натолкнулся на проблему в ORDER BY, но там дело было в том что индекс был по возрастанию (ASC) а сортировка по убыванию (DESC). В конце концов пришлось два индекса создавать. MySQL такими вещами не страдает?
                                                  +1
                                                  конечно, не зная всех деталей точно сказать нельзя. Но вообще-то нет, не страдает. MySQL может ходить по индексу и взад и вперед. Так что вот такой запрос
                                                  select * from t1 ORDER BY ts DESC

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

                                                    Только в частном случае — если это COUNT(*) и нет GROUP BY и WHERE, то есть нужно подставить просто количество строк в таблице, и таблица MyISAM (ну или HEAP, но не InnoDB) — тогда знает.
                                                      0
                                                      MySQL определяет селективность индекса, делая случайные «нырки» в индекс перед выполнением запроса.

                                                      Есть также простейшая статистика, которая собирается ANALYZE TABLE.

                                                      В MariaDB велась также работа над более продвинутой статистики.
                                                      0
                                                      Автору поста вопрос: версия MySQL?

                                                      Также неплохо было бы выложить SHOW CREATE TABLE на t, p и u.
                                                        0
                                                        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
                                                          0
                                                          а зачем используется разный тип движков? в phpbb3_posts InnoDB, в других MyISAM?
                                                            0
                                                            Это с тестового сервера дамп, видимо осталось от экспериментов. На боевом MyISAM везде.
                                                          0
                                                          Вместо сброса кеша удобно использовать SELECT SQL_NO_CACHE ...
                                                            0
                                                            Для 100% уверенности в полученном результате я решил мускуль рестартнуть — он же грузит в память таблицы, к которым были обращения и т.п.
                                                              0
                                                              Да, тоже столкнулся с проблемой, что нужно из одной таблицы отсортированные данные. но условия нужно чекать по другим и случается жесть. Думал, уже, какой-то хитрый кэш прикручивать, чтоб из одной таблицы можно было и условия указать и сортировку сделать, но STRAIGHT_JOIN хорошо помог :)
                                                              Спасибо за информацию.
                                                                0
                                                                За SELECT t.*,… — уже сразу нужно отбивать руки.

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

                                                                Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

                                                                Самое читаемое