Выборка мин/макс значений для групы записей

MySQL/PostgreSQL/SQLite/Oracle/M$SQL/....

Модератор: terminus

Правила форума
Убедительная просьба юзать теги [code] при оформлении листингов.
Сообщения не оформленные должным образом имеют все шансы быть незамеченными.
Аватара пользователя
BlackCat
прапорщик
Сообщения: 469
Зарегистрирован: 2007-10-16 22:40:42

Выборка мин/макс значений для групы записей

Непрочитанное сообщение BlackCat » 2008-11-17 15:22:46

Предыстория

Есть некая софтина работающая на php с базой в MySQL. Есть таблица примерно такого вида:

Код: Выделить всё

CREATE TABLE `main_data` (
  `id` mediumint(6) unsigned NOT NULL auto_increment,
  `created` int(10) unsigned NOT NULL,                         -- дата создания записи в формате метки time()
  `name` varchar(127) NOT NULL,                                 -- имя записи
  `type` tinyint(2) unsigned NOT NULL,              -- type и ext_id Служат для связи с др. таблицами
  `ext_id` smallint(5) unsigned NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM;
Данные могут добавляться и удаляться из таблицы. Что бы не выводить все записи на одной странице прикручен многостраничный интерфейс (ссылки на страницы под списком: "1 2 3 4 ... 50"). Так же возможны два способа сортировки "По имени" и "По дате создания".

Цель
К каждой ссылке на страницу сделать всплывающую подсказку с мин и макс значениями поля сортировки на странице, что бы пользователи не перебирали все страницы подряд, а пробежавшись по подсказкам нашли нужные записи. Подсказка должна выглядеть примерно так:

Код: Выделить всё

С 10.10.2007
По: 26.10.2007
Задача
Нужно определить для кажной группы записей попадающей на одну страницу эти минимальные и максимальные значения.

Хостинговая компания Host-Food.ru
Хостинг HostFood.ru
 

Услуги хостинговой компании Host-Food.ru

Хостинг HostFood.ru

Тарифы на хостинг в России, от 12 рублей: https://www.host-food.ru/tariffs/hosting/
Тарифы на виртуальные сервера (VPS/VDS/KVM) в РФ, от 189 руб.: https://www.host-food.ru/tariffs/virtualny-server-vps/
Выделенные сервера, Россия, Москва, от 2000 рублей (HP Proliant G5, Intel Xeon E5430 (2.66GHz, Quad-Core, 12Mb), 8Gb RAM, 2x300Gb SAS HDD, P400i, 512Mb, BBU):
https://www.host-food.ru/tariffs/vydelennyi-server-ds/
Недорогие домены в популярных зонах: https://www.host-food.ru/domains/

Аватара пользователя
BlackCat
прапорщик
Сообщения: 469
Зарегистрирован: 2007-10-16 22:40:42

Re: Выборка мин/макс значений для групы записей

Непрочитанное сообщение BlackCat » 2008-11-17 15:39:49

Нашел два решения, но оба основаны на переиндексации (в соответствии со способом упорядочивания) записей и использовании временной таблицы.
Решение 1
С копированием данных во временную таблицу.

Код: Выделить всё

--
-- Sort by name alhorithm
--

-- Create tmp table
CREATE TEMPORARY TABLE `sort_by_name` (
  `id`   mediumint(6) unsigned NOT NULL auto_increment,
  `name` varchar(127) NOT NULL,
  KEY  (`id`)
);

-- Copy data
INSERT INTO `sort_by_name` (`name`) SELECT `name` FROM `main_data` ORDER BY `name`;

-- Define items per page const
SET @items:=25;

-- Recreate keys (determine pages)
UPDATE `sort_by_name` SET `id`= (`id` - 1 - MOD(`id`-1,@items))/@items;

-- Show intervals
SELECT `id`,MAX(name), MIN(name) FROM `sort_by_name` GROUP BY `id`;
Решение 2
Копирование ключа и объединение таблиц.

Код: Выделить всё

--
-- Sort by name alhorithm V 2
--

-- Create temporary table
CREATE TEMPORARY TABLE `sort` (
  `page`     mediumint(6) unsigned NOT NULL auto_increment,
  `data_id`  mediumint(6) unsigned NOT NULL ,
  KEY  (`page`)
);

-- Copy data
INSERT INTO `sort` (`data_id`) SELECT id FROM main_data ORDER BY name;

-- Define items per page const
SET @items:=25;

-- Recreate keys (determine pages)
UPDATE `sort` SET `page`= (`page` - 1 - MOD(`page`-1,@items))/@items;

-- Show intervals
SELECT sort.`page`,`id`,MAX(name), MIN(name) FROM `main_data`,`sort` WHERE data_id=id GROUP BY sort.page;
Сортировка по дате создания отличается только типом полей.

Не смотря на копирование данных (строки) в первом решении оно выигрывает у второго, т.к. во втором случае приходится объединять таблицы. Различия начинают проявляться при количестве записей > 10K. Сейчас реализован первый вариант.

Есть у кого-нибудь соображения по поводу эффективности сих решений?

zg
полковник
Сообщения: 5845
Зарегистрирован: 2007-12-07 13:51:33
Откуда: Верх-Нейвинск

Re: Выборка мин/макс значений для групы записей

Непрочитанное сообщение zg » 2008-11-23 14:13:19

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

Аватара пользователя
BlackCat
прапорщик
Сообщения: 469
Зарегистрирован: 2007-10-16 22:40:42

Re: Выборка мин/макс значений для групы записей

Непрочитанное сообщение BlackCat » 2008-11-23 16:47:56

zg писал(а):это проблемы пхп, а не мускуля, пхп рисует страницу
zg писал(а):если записей на странице меньше нескольких тысяч, то пхп успешно справится с их сортировкой и выборкой нужных значений
Получается, проще забрать из БД всю таблицу и обработать ее средствами пхп?
=====
Если вдруг меня не правильно поняли, вот пример, как это должно работать.
pg_1.png
Текущая страница 1, в подсказке описание записей второй страницы.
pg_1.png (1.17 КБ) 3110 просмотров
pg_2.png
Текущая страница 2, в подсказке описание записей первой страницы.
pg_2.png (1.14 КБ) 3110 просмотров
В примере только две страницы, но будет их намного больше.

zg
полковник
Сообщения: 5845
Зарегистрирован: 2007-12-07 13:51:33
Откуда: Верх-Нейвинск

Re: Выборка мин/макс значений для групы записей

Непрочитанное сообщение zg » 2008-11-23 17:06:46

BlackCat писал(а):Получается, проще забрать из БД всю таблицу и обработать ее средствами пхп?
ну всё относительно, если записей меньше тысячи, то да. Если больше, то надо перекладывать работу на мусю и оптимизировать базу под задачу.

Но
BlackCat писал(а):SELECT `id`,MAX(name), MIN(name) FROM `sort_by_name` GROUP BY `id`;
это тоже не подходит для большого количества строк, как и временная таблица для выбора никому ненужной информации, которой даже на странице нет как таковой.

Аватара пользователя
BlackCat
прапорщик
Сообщения: 469
Зарегистрирован: 2007-10-16 22:40:42

Re: Выборка мин/макс значений для групы записей

Непрочитанное сообщение BlackCat » 2008-11-24 12:52:20

zg писал(а):ну всё относительно, если записей меньше тысячи, то да. Если больше, то надо перекладывать работу на мусю и оптимизировать базу под задачу.
Насчет оптимизации. Была идея добавить для каждого поля, по которому выполняется сортировка, дополнительное поле в котором храниться номер страницы на которую попадет запись при данной сортировке. И обновлять эти дополнительные поля при внесении изменений в основное поле таблицы, в т.ч. при добалении/удалении целой записи. Т.е. поддерживать альтернативный индекс.
zg писал(а):Но
BlackCat писал(а):SELECT `id`,MAX(name), MIN(name) FROM `sort_by_name` GROUP BY `id`;
это тоже не подходит для большого количества строк, как и временная таблица для выбора никому ненужной информации, которой даже на странице нет как таковой.
С этим то же почти согласен. "Почти" - потому, что другого способа выполнить ту же операцию нет :unknown: (или он мне неизвестен).

MAK
ст. сержант
Сообщения: 344
Зарегистрирован: 2008-09-17 2:23:21

Re: Выборка мин/макс значений для групы записей

Непрочитанное сообщение MAK » 2008-11-27 15:10:34

BlackCat писал(а):Насчет оптимизации. Была идея добавить для каждого поля, по которому выполняется сортировка, дополнительное поле в котором храниться номер страницы на которую попадет запись при данной сортировке. И обновлять эти дополнительные поля при внесении изменений в основное поле таблицы, в т.ч. при добалении/удалении целой записи. Т.е. поддерживать альтернативный индекс.
Тут тоже надо быть аккуратным - потому что логика работы этого индекса может стать слижком сложной.
Я бы еще посмотрел в сторону кэшировния - почему бы не класть эти данные в кэш? Да хоть в файлы. Сбрасываться они будут только при добавлении очередной записи. А для вывода странички(точнее пейджера) потребуется столько дополнительных запросов, сколько цифр в пейджере. В случае наличия индекса, а его тут явно не хватает, запросы копеечные.
ЗЫ. Поля, по которым производится сортитовка, обязательно проиндексировать!
Вообще желательно все поля, которые учавстутвуют в WHERE, SORT, ORDER, GROUP - индексировать. Без фанатизма конечно.

Аватара пользователя
BlackCat
прапорщик
Сообщения: 469
Зарегистрирован: 2007-10-16 22:40:42

Re: Выборка мин/макс значений для групы записей

Непрочитанное сообщение BlackCat » 2008-11-28 18:05:44

Несколько выводов сделаных лично для себя:
1. Судя по тому что приведеные решения не названы в корне ошибочными и эффективность устраивает - можно ничего не менять пока не потребуется увеличение эффективности (сохранения начальной).
2. Не обязательно все обрабатывать MySQL'ом можно некоторые данные в приложении (скрипте) обработать.
3. Для повышения скорости придется менять таблицу и вводить кеширование.

Всем спасибо за ответы, тему можно закрывать.

Аватара пользователя
ADRE
майор
Сообщения: 2645
Зарегистрирован: 2007-07-26 8:53:49
Контактная информация:

Re: Выборка мин/макс значений для групы записей

Непрочитанное сообщение ADRE » 2008-12-02 23:28:51

сортировка по дате: добавь колонку с никсовым временем, запросом типа если больше или меньше этого, то формируется страница, кеш на 30 минут, при посещении 1000 пользователей должно отлично вытягиваться.....
//del