Perfection- данное сообщество посвящено проблемам оптимизации работы Mysql а так же различным трюкам, с помощью которых эти проблемы можно решить.
Для комментирования сообщений в сообществе - необходимо в него вступить
Преже чем написать комментарий - рекоммендуется прочитать правила сообщества
Если есть желание вступить в сообщество, и есть опыт, которым хотелось бы поделиться - читайте описание сообщества

Пишу сюда все реже, а багаж интересных решений растет :)

Предыстория весьма проста. Существует огромная myisam таблица. В которой есть поля, назовем их для примера id, name

По полю id - Primary, Autoincrement

в таблице много миллионов записей.

И вот, в один прекрасный момент, мы получаем задание: сделать так, чтоб при добавлении name стало уникальным.

Решение в лоб: альтер таблицы, либо переведением ее в InnoDB и использование транзакций в 2 запроса, либо добавлением UNIQUE KEY на поле name.

Решение в лоб предполагает ряд проблем:

Alter по таблице будет ехать около часа.  За это время прийдется либо останавливать сервис, либо сливать таблицу, а потом доливать инкремент.  UNIQUE KEY оказался вообще недопустимимым в данной задаче, так как ранее возможно были дубли, альтер в лоб не поедет + не было задачи удалить дубли.

Решение родилось в одном маленьком запросе:

INSERT INTO t (name) SELECT 'NAME' FROM t t1 LEFT JOIN t t2 ON t2.name='NAME' WHERE t1.id=(SELECT MIN(id) FROM t) AND t2.id IS NULL

Объяснение: Все очень просто, берем любой существующий ряд таблицы (в данном случае проще всего взять первый, с минимальным первичным ключом) и в зависимости от того есть ли такое имя - возвращаем его, или не возвращаем.

Давно сюда не писал Вот уже давно использую еще один трюк с переменными для пересчета различных счетчиков. Конечно, данный запрос можно было упростить до двух или же вообще представить вложенными запросами, но я не люблю вложенных запросов.

Итак, есть 2 таблицы:

CREATE TABLE `a` (
  `id` TINYINT(3) UNSIGNED NOT NULL AUTO_INCREMENT,
  `countb` TINYINT(3) UNSIGNED NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251
...
Читать

Совершенно случайно ищешь решение одной задачи - а находишь совершенно другой, нелепой, может быть даже бессмысленной. Итак - как используя средства MySQL просчитать n-й член какой-либо математической последовательности. В приведенном ниже примере мы просчитываем 10 элемент ряда Фибоначчи (следующий член последовательности равен сумме двух предыдущих). В принципе можно заложить в вычисление любую математическую последовательность. Естественно данный метод не очень быстрый.

SET @a := 0;
SET @b := 1;
SET @temp := 0;
select BENCHMARK(10,(select  @b FROM (SELECT 1) t WHERE (@temp:=@a)>-1  AND (@a:=@b)>-1  AND (@b:=@b+@temp)>-1)),  @temp;

 

Наткнулся на очень полезную презентацию.

 

К сожалению, не во всех случаях, где хотелось бы использовать LIMIT это разрешается функционалом MySQL.
Например следующие конструкции будут работать

SELECT * FROM table LIMIT 1
SELECT * FROM t1,t2 WHERE t1.id=t2.id LIMIT 1
DELETE FROM table LIMIT 1

а эта - нет
...
Читать

Существует задача - проапдейтить запись, но при этом получить ее предыдущее значение.
Решение в лоб

SELECT value FROM table WHERE id='$id';
UPDATE table SET value='$newvalue' WHERE id='$id';

Но. Мы делаем лишний запрос, как же обойтись без него? Давайте попробуем использовать переменные.

UPDATE table SET value='$newvalue' WHERE id='$id' AND IF((@oldvalue := value),1,1);
SELECT @oldvalue;

Итак мы избавились от лишнего запроса, условие IF необходимо для того, чтоб в любом случае значение в присвоении было равно 1 (если value=0 или value is NULL @oldvalue := value вернет 0 или NULL и условие WHERE провалится)

Есть таблица с некими статьями. У каждой статьи есть свой AUTO_INCREMENT Id. Но возможен вариант, что следующая и предыдущая статьи не обязательно будут иметь идентификатор Id+1 и Id-1.

Читать

Задача: Определить есть ли строки в которых искомая строка входит в последовательность и стоит на определенном месте. Последовательность состоит из строк произвольной длинны разделенных разделителем «|» и может содержать любые символы кроме символа разделителя.
Где такое может использоваться?
Используется такая схема, к примеру, на квестах, где есть группа вопросов, и ответ на вопрос может быть составной, тоесть может иметь несколько вариантов ответов. В примере ниже во второй позиции перечислены все варианты ответов на вторую часть вопроса (Пушкин, Pushkin). Поскольку таких строк не более пары десятков, то производительностью мы можем пренебречь и назвать такой способ хранения ответов удобным :lol:
Для наглядности немного упрощу пример. Есть в базе строки-последовательности в поле `field` вида
F|R|D|G
G|L|H|Y
N|M|Y|X
Необходимо проверить входит ли элемент “L” в какую-нибудь последовательность во второй позиции.
Решение:
SELECT COUNT(*) FROM `table` WHERE SUBSTRING_INDEX(`filed`, ‘|’, 2) LIKE ‘%L’;
Этот запрос возвращает ноль или единицу, тоесть правильно ответили или нет.
Работает этот запрос следующим образом:
сначала поле с последовательностью обрезаем до 2го символа «|» получаем строку F|R дальше смотрим есть ли в этой строке в конце искомая строка, тоесть ответ на часть вопроса.
При функцию SUBSTRING_INDEX можно почитать здесь http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_substring-index

Вещь простая, но, как оказалось, не все знают, что для таблиц, созданных с помощью конструкций CREATE TABLE ... SELECT  возможно объявлять индексы, а также задавать любые другие параметры таблицы прямо в этом операторе.  Это позволяет  значительно соптимизировать создание индексов для таблиц типа MyISAM.


Читать