Версия для печати темы Нажмите сюда для просмотра этой темы в оригинальном формате

Форум программистов > MySQL > Помогите оптимизировать запрос

Автор: animegirl 1.9.2012, 01:47

Код UPDATE `way`.`mail_overview` SET `mail_overview`.`last_message`=     (SELECT `mail_messages`.`text`     FROM `way`.`mail_messages`      WHERE `mail_messages`.`did`=`mail_overview`.`did`         AND ((`mail_messages`.`fid`=`mail_overview`.`uid` AND `mail_messages`.`fid_deleted`=2) OR (`mail_messages`.`tid`=`mail_overview`.`uid` AND `mail_messages`.`tid_deleted`=2))     ORDER BY `mail_messages`.`time` DESC     LIMIT 1), `mail_overview`.`last_message_time`=     (SELECT `mail_messages`.`time`     FROM `way`.`mail_messages`      WHERE `mail_messages`.`did`=`mail_overview`.`did`         AND ((`mail_messages`.`fid`=`mail_overview`.`uid` AND `mail_messages`.`fid_deleted`=2) OR (`mail_messages`.`tid`=`mail_overview`.`uid` AND `mail_messages`.`tid_deleted`=2))     ORDER BY `mail_messages`.`time` DESC     LIMIT 1) WHERE `mail_overview`.`did`=1 Есть какой-нибудь ход, не делать два селекта одной и той же строки?

Автор: tzirechnoy 1.9.2012, 17:56

Вряд ли. LIMIT 1, да и ORDER на самом деле -- это очень нереляцыонный конструкт. Хотя, возможно, сработает что-то вроде Код INSERT INTO way.mail_overview (id, last_message, last_message_time)    SELECT mo.id, mm.text, mm.time        FROM way.mail_overview mo             LEFT JOIN way.mail_messages mm ON mo.did=mm.did                       AND ( (mm.fid=mo.uid AND mm.fid_deleted=2)                            OR                            (mm.tid=mo.uid AND mm.tid_deleted=2) )       WHERE mo.did=1       ORDER BY mm.time  ON DUPLICATE KEY UPDATE last_message=VALUES(last_message), last_message_time=VALUES(last_message_time) Но, на самом деле, это бабушка на двое сказала, что при многих вариантах совпадения выборки из mail_messages будет установлено именно последнее значение. В общем, лучшэ не рисковать. (Да, mail_overview.id -- имеется в виду PRIMRAY KEY. Вместо него можно подставить любой другой UNIQUE, в т.ч. составной) А Вы уверены, что оптимизатор не преобразовал этот запрос к одному? А то, можэт, и оптимизировать здесь не требуется?

Автор: animegirl 1.9.2012, 18:11

Цитата(tzirechnoy @  1.9.2012,  17:56 ) А Вы уверены, что оптимизатор не преобразовал этот запрос к одному? А то, можэт, и оптимизировать здесь не требуется? А есть возможность это увидит?

Автор: Akina 2.9.2012, 20:06

Код UPDATE   `way`.`mail_overview` , ( SELECT       `mail_messages`.`text`     , `mail_messages`.`time`     FROM        `way`.`mail_messages`      WHERE       `mail_messages`.`did`=`mail_overview`.`did`       AND ((`mail_messages`.`fid`=`mail_overview`.`uid` AND `mail_messages`.`fid_deleted`=2) OR (`mail_messages`.`tid`=`mail_overview`.`uid` AND `mail_messages`.`tid_deleted`=2))     ORDER BY       `mail_messages`.`time` DESC     LIMIT 1   ) `subquery` SET   `mail_overview`.`last_message`= `subquery`.`text` , `mail_overview`.`last_message_time`= `subquery`.`time` WHERE   `mail_overview`.`did`=1 PS. Структуру подзапроса не трогал. PPS. Он кривой.

Автор: tzirechnoy 3.9.2012, 10:41

Цитата Код UPDATE   `way`.`mail_overview` , ( SELECT А какая версия? 5.1.63 не даёт в подзапросе в джойне ссылаться на другие таблицы из того жэ джойна. Unknown column, в данном случае будет unknown column `mail_overview`.`did` in where clause. Это, в общем, логично -- поскольку отношэния создаются/выбираются не по очереди в каком-то порядке, тем более не по очереди для каждой записи предыдущих отношэний выбирается следующее -- а все отношэния JOINа существуют до начала объединения.  Сделать противоестественный интеллект, который бы определял, что для подзапросов в джойне нужны такие последовательные переборы -- наверное можно, но в общем нетривиально, и мне было бы любопытно, если бы он где-то появился. Добавлено через 35 секунд [quote]А есть возможность это увидит?[/quote Поиграйтесь с explain.

Автор: animegirl 3.9.2012, 10:43

tzirechnoy, да, она самая 5.1.63 Akina, кто иммено и почему?

Автор: tzirechnoy 3.9.2012, 10:45

А, да, explain UPDATE появился только в 5.6.3. Beware, как говорится. В остальных -- можно переписать UPDATE на равнозначный SELECT. Добавлено через 54 секунды Цитата tzirechnoy, да, она самая 5.1.63 Да это я с Akina общался.

Автор: Akina 3.9.2012, 11:57

Цитата(tzirechnoy @  3.9.2012,  11:41 )  5.1.63 не даёт в подзапросе в джойне ссылаться на другие таблицы из того жэ джойна. Unknown column, в данном случае будет unknown column `mail_overview`.`did` in where clause Не понял... ведь во внешнем запросе идёт отбор по `mail_overview`.`did`=1, во внутреннем  отсутствует группировка, следовательно, всё вырождено, подзапрос оперирует ТОЛЬКО данными таблицы mail_messages, внешний запрос - ТОЛЬКО данными таблицы mail_overview и подзапроса... Именно это в первую очередь я и имел в виду, говоря, что запрос кривой. На черезпопные условия отбора в нём можно не обращать внимания, это мелочи.

Автор: animegirl 3.9.2012, 13:11

[Б]Акина[/Б], а его не надо групировать, он в overview - primary key

Автор: tzirechnoy 3.9.2012, 13:22

Цитата во внутреннем  отсутствует группировка, следовательно, всё вырождено, подзапрос оперирует ТОЛЬКО данными таблицы mail_messages  Прямщас вырождено. AND ((`mail_messages`.`fid`=`mail_overview`.`uid` ... Впрочем, did как константу тожэ движок протаскивать не будет (хотя это можно было и руками сделать).

Автор: Akina 3.9.2012, 14:06

Цитата(tzirechnoy @  3.9.2012,  14:22 ) Прямщас вырождено Развяжи ВЕСЬ запрос. Приведи подобные в диком условии подзапроса. Связывание внутреннее, и условие связывания легко выносится наружу.

Автор: tzirechnoy 3.9.2012, 15:06

А давай ты сам этим пострадаешь? Тем более, что ты вроде знаешь, как. И вообще, версию мыскля, на которой это или что-то такое у тебя работает -- в студию. А если ни на какой не проверял, то нечего тут трындеть попусту.

Автор: Akina 3.9.2012, 15:29

Хамить совершенно необязательно. Но это к слову. То, что написано - работать не будет, и я об этом не раз говорил. Подзапрос - кривой. Не помнишь? жаль. На какой версии оно будет работать, если вообще когда-нибудь будет - мне по барабану. Тебе интересно? пробуй. Если выполнить корректировку запроса, то возможно его привести к виду, когда подзапрос оперирует данными одной таблицы, а запрос - данными другой таблицы и подзапроса. Причём при полном сохранении логики. Я об этом говорил. И такой откорректированный запрос будет работать. Не знаешь как? и не надо. Задевает? мне это пофиг.

Автор: tzirechnoy 3.9.2012, 18:17

Цитата Хамить совершенно необязательно. Но это к слову. Конечно, необязательно. Но иногда -- полезно. Способствует быстрому взаимопониманию. Цитата То, что написано - работать не будет, и я об этом не раз говорил. Значит, невнятно говорил. Цитата Подзапрос - кривой. Не помнишь? жаль. Это я, конечно, помню, но к делу это не относится. Цитата Не знаешь как? и не надо. Задевает? мне это пофиг. Вот только и топикстартер тожэ не знает. Да и ты -- тожэ. Зачем в таком случае бросаться утверждениями, и кто ты такой, если на самом деле привести его нельзя -- оставляю подумать участникам в качестве разминки.

Автор: Akina 3.9.2012, 18:27

Цитата(tzirechnoy @  3.9.2012,  19:17 ) топикстартер тожэ не знает Топикстартеру сказано, что надо переписать запрос. И я пока не вижу попыток ТС это сделать. Зато я прекрасно помню предыдущие темы ТС и некоторые утверждения в них - посему пока ТС не начнёт реально что-то делать, я и пальцем не шевельну. Цитата(tzirechnoy @  3.9.2012,  19:17 ) Да и ты -- тожэ. Учись отвечать только за себя. И не надо пробовать брать меня на "слабо".

Автор: animegirl 3.9.2012, 18:33

Akina, а как его ещё переписать? Этот вариант работает, логика задачи работает. Я так поняла, что либо движок сам экономит один селект, либо мне без него не обойтись, так как вы сами написали, что ваш вариант не рабочий. Да я могу в принципе только в одном месте подправить, а именно заменить "`mail_messages`.`did`=`mail_overview`.`did`" на "`mail_messages`.`did`=$вар", она из ПХП приходит, больших путей для изменений я тут не вижу

Автор: Akina 3.9.2012, 19:58

animegirl, давайте по шагам. Сначала возьмём вот этот фрагмент: Код ((`mail_messages`.`fid`=`mail_overview`.`uid` AND `mail_messages`.`fid_deleted`=2) OR (`mail_messages`.`tid`=`mail_overview`.`uid` AND `mail_messages`.`tid_deleted`=2)) Перепишите его так, чтобы логика отбора полностью сохранилась, но `mail_overview`.`uid` использовался только один раз. Сделав это, отвлекитесь и ответьте на такой вопрос: почему в подзапросе потребовался LIMIT?

Автор: animegirl 3.9.2012, 23:58

Потому, что во второй таблице идёт сверка по трём столбцам, и не один из них не уник, оно может повторятся, а мне нужен только последний по времени. А никак его не переписать, на словах выглядит так: фид - отправител тид - получател фид_дел - отправитель удалил тид_дел - получатель удалил уид - один из них, в таблице овервив есть уид и уид2, уид2 используется в другом случае, поэтому он тут не представлен. Так что, либо фид равен уид и фид_дел не удалён, либо тид равен уид и тид_дел не удалён Запутано, но думаю понятно или?

Автор: Akina 4.9.2012, 11:44

Цитата(animegirl @  4.9.2012,  00:58 ) А никак его не переписать animegirl, я, оказывается, был невнимателен. В длинных именах не заметил, что на равенство 2 проверяются разные поля. Ок, свёртка условия отпадает. Теперь остался последний невыясненный вопрос. В исходном запросе в подзапросе отбираются строки в т.ч. и по условию `mail_messages`.`did`=`mail_overview`.`did` А в запросе на обновление используется отбор по `mail_overview`.`did`=1 Это не смущает? Кстати, что это за поле - did?

Автор: tzirechnoy 4.9.2012, 11:56

Цитата Связывание внутреннее, и условие связывания легко выносится наружу. Условие -- конечно, выносится, чего там выносить. И приводить ничего не надо -- как есть в WHERE или ON пихаешь. ORDER BY .. LIMIT 1 -- не выносится, оно нереляцыонно.

Автор: animegirl 4.9.2012, 16:59

Akina, мид - мэссаджИД дид - диалогИД В принципе, я могу в середине тоже поставить "1", вообще эта 1 она не фиксирована, там ПХП переменная Таблицы: Код CREATE TABLE IF NOT EXISTS `mail_overview` (   `did` int(10) unsigned NOT NULL AUTO_INCREMENT,   `uid` int(10) unsigned NOT NULL,   `uid2` int(10) unsigned NOT NULL,   `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,   `name2` varchar(255) COLLATE utf8_unicode_ci NOT NULL,   `last_message` varchar(31) COLLATE utf8_unicode_ci NOT NULL,   `last_message2` varchar(31) COLLATE utf8_unicode_ci NOT NULL,   `last_message_time` int(10) unsigned NOT NULL,   `last_message_time2` int(10) unsigned NOT NULL,   `readed` tinyint(1) unsigned NOT NULL COMMENT '2 - new, 1- readed',   PRIMARY KEY (`did`) ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=2 ; Код CREATE TABLE IF NOT EXISTS `mail_messages` (   `did` int(10) unsigned NOT NULL,   `mid` int(10) unsigned NOT NULL AUTO_INCREMENT,   `fid` int(10) unsigned NOT NULL,   `tid` int(10) unsigned NOT NULL,   `from` varchar(255) COLLATE utf8_unicode_ci NOT NULL,   `text` longtext COLLATE utf8_unicode_ci NOT NULL,   `readed` tinyint(1) unsigned NOT NULL COMMENT '2 - new, 1- readed',   `fid_deleted` tinyint(1) unsigned NOT NULL COMMENT '2 - show, 1 - hide',   `tid_deleted` tinyint(1) unsigned NOT NULL COMMENT '2 - show, 1 - hide',   `time` int(10) unsigned NOT NULL,   UNIQUE KEY `mid` (`mid`),   KEY `did` (`did`) ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=14 ;

Автор: Akina 4.9.2012, 17:21

Цитата(Akina @  4.9.2012,  12:44 ) В исходном запросе в подзапросе отбираются строки в т.ч. и по условию `mail_messages`.`did`=`mail_overview`.`did` А в запросе на обновление используется отбор по `mail_overview`.`did`=1 Поскольку `mail_overview`.`did`- уникальный индекс, идентифицирующий диалог, что получается? Вот подзапрос Код     (SELECT `mail_messages`.`text`     FROM `way`.`mail_messages`      WHERE `mail_messages`.`did`=`mail_overview`.`did`         AND ((`mail_messages`.`fid`=`mail_overview`.`uid` AND `mail_messages`.`fid_deleted`=2) OR (`mail_messages`.`tid`=`mail_overview`.`uid` AND `mail_messages`.`tid_deleted`=2))     ORDER BY `mail_messages`.`time` DESC     LIMIT 1) Это мы выбираем все сообщения всех диалогов пользователя из таблицы сообщений, берём самое последнее его сообщение (пофиг, из какого оно диалога! отбора по did ещё нет)... А потом мы апдейтим этим сообщением (и его временем соответственно) диалог номер 1 (did=1) этого пользователя в таблице обзоров? Что-то я не понимаю логики происходящего...

Автор: animegirl 4.9.2012, 17:26

Цитата(Akina @  4.9.2012,  17:21 ) пофиг, из какого оно диалога! отбора по did ещё нет Как нету, а  Код WHERE `mail_messages`.`did`=`mail_overview`.`did`

Автор: Akina 4.9.2012, 19:34

Цитата(animegirl @  4.9.2012,  18:26 ) Как нету, а  На момент выполнения подзапроса секция where внешнего запроса ещё не применялась, соответственно будут отобраны все записи всех диалогов. В т.ч. и диалогов, еоторые не соответствуют условию отбора по did внешнего подзапроса, но соответствуют остальным условиям. Внешнее же условие ограничивает обновляемые записи, но никак не отбираемые в подзапросе. Попробуйте внешнее условие `mail_overview`.`did`=1 транслировать в подзапрос, заменив поле константой Код     (SELECT `mail_messages`.`text`     FROM `way`.`mail_messages`      WHERE `mail_messages`.`did`= 1         AND ((`mail_messages`.`fid`=`mail_overview`.`uid` AND `mail_messages`.`fid_deleted`=2) OR (`mail_messages`.`tid`=`mail_overview`.`uid` AND `mail_messages`.`tid_deleted`=2))     ORDER BY `mail_messages`.`time` DESC     LIMIT 1)

Автор: animegirl 4.9.2012, 19:37

О! Теперь поняла, спасибо, сейчас исправлю Добавлено через 18 секунд А как посмотреть то в итоге, сколько селектов он делает?

Автор: Akina 4.9.2012, 21:15

animegirl, давайте подойдём с другой стороны. Структуру таблиц мы уже видели. Пояснения, кто есть ху - более-менее тоже, хотя лучше было бы поподробнее. Теперь сформулируйте собственно задачу. И попробуем её решать.

Автор: Akina 12.9.2012, 09:26

Хм... вопрос помечен как решённый...

Автор: animegirl 13.9.2012, 00:07

Akina, Всё верно, логика в запросе верная, я думала, может есть возможность по другому записать, но видимо оно и так верно. Спасибо, что подсказали поменять переменные на числа.

Автор: Akina 13.9.2012, 06:52

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

Автор: animegirl 13.9.2012, 06:55

Akina, Вы про subquery или я не внимательно читала ваши ответы?

Автор: Akina 13.9.2012, 07:05

animegirl, я про то, что можно избавиться от ссылок на поля внешнего запроса в подзапросах. А подзапросы соответственно перенести из SELECT в FROM.

Автор: animegirl 13.9.2012, 07:09

Ну первое я сделала, я заменила Код SELECT `mail_messages`.`text`     FROM `way`.`mail_messages`      WHERE `mail_messages`.`did`=`mail_overview`.`did`         AND ((`mail_messages`.`fid`=`mail_overview`.`uid` AND `mail_messages`.`fid_deleted`=2) OR (`mail_messages`.`tid`=`mail_overview`.`uid` AND `mail_messages`.`tid_deleted`=2))     ORDER BY `mail_messages`.`time` DESC     LIMIT 1 на Код SELECT `mail_messages`.`text`     FROM `way`.`mail_messages`      WHERE `mail_messages`.`did`=1         AND ((`mail_messages`.`fid`=`mail_overview`.`uid` AND `mail_messages`.`fid_deleted`=2) OR (`mail_messages`.`tid`=`mail_overview`.`uid` AND `mail_messages`.`tid_deleted`=2))     ORDER BY `mail_messages`.`time` DESC     LIMIT 1 А поповоду второго, что-то не допоняла, это как?

Автор: Akina 13.9.2012, 08:44

Цитата(animegirl @  13.9.2012,  08:09 ) поповоду второго, что-то не допоняла, это как? Ну у тебя идёт в запросе  Код SET `поле` = (SELECT ...)  и я подозреваю (впрочем, не убеждён), что сервер выполняет запрос для каждой записи. А я предлагаю конвертировать его в  Код SET `поле` = `подзапрос`.`значение` from `таблица`, (SELECT ...) `подзапрос`  в этом случае подзапрос будет гарантированно выполняться один раз.

Автор: animegirl 13.9.2012, 08:48

Вроде бы бодра, но ночь была длинной. Примерно я понимаюо чём речь идёт. А можно рабочий пример? Ну так, чтоб у меня всё расставилось на свои места. Просто то что вы написали выглядит наводкой, синтаксис ведь так не пропустит Добавлено через 32 секунды не обязательно по моим таблицам, просто две таблицы с двумя полями, абстрактно, но верно. Спасибо

Автор: Akina 13.9.2012, 08:56

Цитата(animegirl @  13.9.2012,  09:48 ) то что вы написали выглядит наводкой это и есть наводка Цитата(animegirl @  13.9.2012,  09:48 ) синтаксис ведь так не пропустит А это с какого перепугу? Код update table1 t1, (select t2.id, max(t2.val) val from table2 t2 group by t2.id) q set t1.val = q.val where t1.id = q.id всяко лучше, чем Код update table1 t1 set t1.val = (select max(t2.val) from table2 t2 where t1.id=t2.id) и тем более чем Код update table1 t1 set t1.val = (select t2.val from table2 t2 where t1.id=t2.id order by t2.val desc limit 1)

Автор: animegirl 13.9.2012, 09:32

МАХ в innoDB сработает лучше чем order by? первый вариант поняла второй поняла чем первый от второго лучше тоже поняла вроде бы даже немного вглядевшись в свой запрос поняла как можно третий избежать Немного поковырявшись... пришла к такому варианту: Код UPDATE `way`.`mail_overview`, (SELECT max(`mail_messages`.`time`) `time`,`mail_messages`.`text`     FROM `way`.`mail_messages`      WHERE `mail_messages`.`did`=1         AND ((`mail_messages`.`fid`=97 AND `mail_messages`.`fid_deleted`=2) OR (`mail_messages`.`tid`=97 AND `mail_messages`.`tid_deleted`=2))) `mail_messages` SET `mail_overview`.`last_message`=`mail_messages`.`text`,`mail_overview`.`last_message_time`=`mail_messages`.`time` WHERE `mail_overview`.`did`=1 везде, где есть цифры, там переменные из ПХП. Застопорилась на одном моменте. Время обновляется как надо. А вот текст берётся от другой записи

Автор: Akina 13.9.2012, 09:37

Цитата(animegirl @  13.9.2012,  10:32 ) Застопорилась на одном моменте. Время обновляется как надо. А вот текст берётся от другой записи  Так и дложно быть. Это и в документации описано. Если выражение выходного набора не включено ни в выражение группировки, ни в выражение аргумента статистической функции, то для его вычисления будет взята случайная запись группы. Именно потому и получается громоздкий подзапрос - нужно получить время, а потом по нему из другой копии запись с тем же временем и текстом именно для него. Но работать всё равно будет быстрее.

Автор: animegirl 13.9.2012, 11:38

Ясно, тогда финальный мазок и остановлюсь на этом Код UPDATE `way`.`mail_overview`, (SELECT `mail_messages`.`text`,`mail_messages`.`time`     FROM `way`.`mail_messages`      WHERE `mail_messages`.`did`=1         AND ((`mail_messages`.`fid`=97 AND `mail_messages`.`fid_deleted`=2) OR (`mail_messages`.`tid`=97 AND `mail_messages`.`tid_deleted`=2))     ORDER BY `mail_messages`.`time` DESC     LIMIT 1) `mail_messages` SET `mail_overview`.`last_message`=`mail_messages`.`text`, `mail_overview`.`last_message_time`=`mail_messages`.`time` WHERE `mail_overview`.`did`=1

Автор: animegirl 13.9.2012, 12:28

Можно сюда же скину другой запрос? Просто таблицы те же Код SELECT * FROM `way`.`mail_overview` WHERE `mail_overview`.`uid`=97 OR `mail_overview`.`uid2`=97 ORDER BY `mail_overview`.`last_message_time`,`mail_overview`.`last_message_time2` ASC Смысл достать последние сообщение в диалоге. Я знаю, что написала чушь в order by, чушь правда работает, но это явно подпиленный костыль, который стоит как можно скорее выпрямить. Даже не знаю как ЭТО можно сделать в логический правильном порядке. По простому человеческому там должно быть так: Код SELECT * FROM `way`.`mail_overview` WHERE `mail_overview`.`uid`=97 ORDER BY `mail_overview`.`last_message_time` OR `mail_overview`.`uid2`=97 ORDER BY `mail_overview`.`last_message_time2` ASC Но как на правильном MySQL синтаксисе написать, мозг никак не может придумать.

Автор: Akina 13.9.2012, 12:36

Код SELECT   CASE WHEN `mail_overview`.`uid`=97 THEN `mail_overview`.`last_message_time` ELSE `mail_overview`.`last_message_time2`END , *  FROM `mail_overview`  WHERE `mail_overview`.`uid`=97 OR `mail_overview`.`uid2`=97  ORDER BY 1 ASC

Автор: animegirl 13.9.2012, 12:39

Akina, #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '* FROM `mail_overview` WHERE `mail_overview`.`uid`=97 OR `mail_overview`.`uid2' at line 3

Автор: igorold 13.9.2012, 14:52

А разве в конструкции ORDER BY может быть OR   ?

Автор: Akina 13.9.2012, 15:37

animegirl, ну разверни звезду в список полей, или добавь опознашку таблицы. Цитата(igorold @  13.9.2012,  15:52 ) А разве в конструкции ORDER BY может быть OR   ?  А почему нет?

Powered by Invision Power Board (http://www.invisionboard.com)
© Invision Power Services (http://www.invisionpower.com)