Синтаксис MySQL не позволяет выполнять команду ORDER BY перед GROUP BY. Сначала ставите GROUP BY и только потом вам позволят поставить ORDER BY.
В некоторых задачах, например, из прошлой записи «10 последних собеседников из мессенджера» нужна сделать именно сначала сортировку и только потом сгруппировать результат.

Решение подсказали на западных форумах: нужно использовать временную таблицу.
В моем варианте это выглядит так:
SELECT * FROM ( SELECT * FROM table ORDER BY id, date DESC ) as inv GROUP BY id ORDER BY date DESC

Слушаю: Изморозь - Колыбельная
Теги записи: mysql

Комменты: закрыты

24 октября 2012

18:06 MySQL. Уменьшение значения, но не ниже нуля

❤ 2

Имеем UPDATE table SET value = value - 1 WHERE id=1

Чтобы уменьшить значение в таблице MySQL только до нуля, не уходя в минус или не перескакивая в максимальное значение нужно добавить к выражению проверку на ноль.

в итоге получаем
UPDATE table SET value = value - 1 WHERE id=1 AND value > 0

Слушаю: Sephiroth - The Call Of The Serpent
Теги записи: mysql

Комменты: закрыты

25 марта 2009

17:26 Как работают «Похожие темы»

❤ 124

Как?
При рендеринге страницы берется название топика и расчленяется на отдельные слова, убирается мусор пунктуации, снабжается логикой «OR».
Полученная строка передается демону Сфинкса, который примерно за 0.001 секунды выдаёт идентификаторы похожих тем из этого и/или тематически схожего раздела, отсортированные по релевантности. К сожалению, на установку соединения с ним и получение результата тратится еще 0.1 секунды.
По id из мускуля достается вся необходимая информация и рисуется блок «похожие темы», который, кроме всего прочего, кэшируется в мэмкеше на сутки, чтобы не тратить ресурсы на повторный рендеринг при показе этой темы.

HINT
Полученный из Сфинкса результат является массивом, где первый элемент самый релевантный. Если засунуть его в MySQL как он есть:
SELECT * FROM topics WHERE id IN ($sphinx_topics)
то MySQL отсортирует его по id, то есть первым станет имеющий самый маленький id и дальше по возрастанию.
Чтобы этого избежать, есть хитрость:
SELECT * FROM topics WHERE id IN ($sphinx_topics) ORDER BY FIELD(id,$sphinx_topics)

Слушаю: Massacre - Chamber of Ages
Теги записи: форум, mysql

Комменты: 0

16 сентября 2008

07:54 Правда о key_buffer

❤ 24

В MySQL есть такая переменная как key_buffer. Она отвечает за размер буфера в памяти, используемого для хранения индексов таблиц. Большинство теоретических сайтов рекомендует настраивать её подобным образом: «Необходимо увеличить это значение настолько, насколько возможно, не менее 15%-25% ОЗУ». Таким образом для стандартного сервера с 4Gb памяти сразу предлагают отдать 1 гигабайт.
Это в корне неверно.
Как правильно настроить key_buffer?
Выделять её нужно столько, сколько занимают у вас индексы + запас на рост таблиц (~10%). Иначе этот буфер у вас кушать драгоценную память бесцельно.
На Демиарте ключи занимают 226Mb и для них создан буфер в 256Mb.

Слушаю: Marilyn Manson - The Red Carpet Grave
Теги записи: mysql

Комменты: 0

29 мая 2008

14:12 Поиск “Sphinx”

❤ 26

Как вы знаете, форум недавно перешел рубеж в миллион записей. Поиск по такой базе данных весьма ресурсоёмкая операция. И даже недавно проведенная оптимизация не сильно улучшила состояние дел. Поисковые запросы, в пиковые часы, могли выполняться по 10-50 секунд. С учетом роста БД, время и затраты ресурсов на поиск продолжали бы рости.
Поэтому, было принято решение отказаться от внутреннего поиска MySQL и перейти на альтернативный вариант.
В качества нового движка был выбран Sphinx — free open-source SQL full-text search engine.
Благодаря месье Prolag, я получил API и модуль для IPB 2.x.
Сегодня я закончил переписывание модуля под нашу версию форума и запустил сфинкс-поиск.

Плюсы:
+ скорость работы на пару порядков выше, чем full-text поиск MySQL;
+ размер базы MySQL уменьшается на треть, за счет удаления индекса full-text;
+ Sphinx успешно обкатан на форумах с несколькими миллионами записей (neowin.net ~ 8 млн. записей; crazymoto.net ~6 млн. записей), а значит, будем надеяться, что место для роста есть

+ускорение добавления объёмных записей в БД, за счет того, что нет необходимости просчитывать full-text индекс.
+ минимальное ограничение на размер искомого слова всего 3 буквы. максимальное — 100.

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

Правила поиска:
1. Наличие минуса перед искомым словом означает, что его не нужно искать.
пример: яблоко -апельсин
будут найдены записи содержащие слово «яблоко» и не содержащие слово «апельсин»
2. Если вам нужно найти точную фразу, то заключайте её в кавычки
пример: "уроки фотошоп"

Слушаю: ДДТ - Белая ночь
Теги записи: форум, mysql

Комменты: 6

25 мая 2008

17:38 Static MySQL на linuxthreads

❤ 14

Второй день тюним с garrotte MySQL на сервере. В итоге собрали статичный на linuxthreads.
В данный момент он работает с нагрузкой более 800 запросов в секунду.

Слушаю: Massacre - Plains of Insanity
Теги записи: сервер, mysql, оптимизация

Комменты: 6

17 мая 2008

11:58 Поиск на форуме

❤ 8

База данных форума, на данный момент, весит больше гигабайта.
Треть её занимает индекс «полнотекстового поиска». Это словарь, в котором перечислены все слова и указано, в каких местах они встречаются. При наличии такого индекса достаточно осуществить поиск нужных слов в нём и тогда сразу же будет получен список документов, в которых они встречаются.
Но даже такая обработка, при некоторых запросах, занимает слишком много времени и нагружает сервер.
Если я не найду способа оптимизировать работу Поиска, то он будет отключен, база данных полегчает на треть, расход памяти уменьшится, нагрузка на сервер снизится, а искать будем через встроенную панель Гугля.

Слушаю: Madonna - She's Not Me
Теги записи: сервер, mysql

Комменты: 2

23 марта 2008

10:49 MySQL 5 update

❤ 1

Утром на сервере обновил MySQL с 4.1.22 до 5.0.51а.
Старая была собрана из бинарников (поставка DA), а эту, из-за отсутствия версии под нашу фрю, пришлось ставить из портов. Ну и прицепом бонусный геммор с изменением всех путей и перекомпиляцией PHP.

Пятый мускуль существенно строже относится к синтаксису запросов. На форуме я уже давно, вроде, привел все в надлежащий вид, а вот из Драконии полезли баги.

Запрос карты мира:
"SELECT a.name, a.alive, a.sex, a.level, a.laston, a.loggedin, a.login, a.pvpflag, b.value AS location, c.clanshort, a.clanrank
FROM accounts a, module_userprefs b ..."

меняем порядок в FROM:
"FROM module_userprefs b, accounts a ..."

Слушаю: Carpathian Forest - Morbid Fascination of Death
Теги записи: draconia, сервер, mysql

Комменты: 0

20 марта 2008

11:41 Mysql Backup

❤ 0

Ежедневный архив с бэкапом форума Демиарта весит 16 гигов плюс 4 гига архивов с субдоменами. Пора менять его организацию. Первое это разделение архивов с БД и файлами. Далее инкрементальная архивация. Хотя бы для файлов.

Differential backup Mysql. Инкрементальный бэкап с diff'ом. Скрипт, который в воскресенье делает полный бэкап mysql, а в остальные дни инкрементальный с воскресным.

Sypex Dumper. PHP-скрипт, с помощью которого можно просто и быстро создать резервную копию (бекап, дамп) базы данных MySQL, а также восстановить в случае необходимости базу данных из резервной копии. Версию 1.1.0 с кроном обещают в этом месяце.

Bacula - кроссплатформенное клиент-серверное программное обеспечение, позволяющее управлять резервным копированием, восстановлением, и проверкой данных по сети для компьютеров и операционных систем различных типов. По-моему слишком громоздкое решения для нашего сервера. Русская документация (1.5Mb)

Бэкапим на Почту Gmail. Правильно, чего там гигабайтам простаивать. Пусть будет резервным хранилищем

Главное не забывать паролить базы.

AutoMySQLBackup. Скрипт для автоматизации резервного копирования и сжатия локального иди удаленного MySQL сервер. Поддерживает выборочный бэкап отдельных баз.

Слушаю: DDT - В бой
Теги записи: сервер, mysql

Комменты: 5

12 марта 2008

21:04 MySQL

❤ 0

дабы не потерялось:

Слушаю: Аквариум - Голубой огонек
Теги записи: софт, mysql

Комменты: 0