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

[BlackCat]

Предыстория

Есть некая софтина работающая на 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

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

[Хостинг 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]

Нашел два решения, но оба основаны на переиндексации (в соответствии со способом упорядочивания) записей и использовании временной таблицы.
Решение 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]

К каждой ссылке на страницу сделать всплывающую подсказку с мин и макс значениями поля сортировки на странице

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

это проблемы пхп, а не мускуля, пхп рисует страницу

Нашел два решения, но оба основаны на переиндексации

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

[BlackCat]

это проблемы пхп, а не мускуля, пхп рисует страницу

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

Получается, проще забрать из БД всю таблицу и обработать ее средствами пхп?
=====
Если вдруг меня не правильно поняли, вот пример, как это должно работать.

Текущая страница 1, в подсказке описание записей второй страницы.

pg_1.png (1.17 КБ) 2345 просмотров

Текущая страница 2, в подсказке описание записей первой страницы.

pg_2.png (1.14 КБ) 2345 просмотров

В примере только две страницы, но будет их намного больше.

[zg]

Получается, проще забрать из БД всю таблицу и обработать ее средствами пхп?

ну всё относительно, если записей меньше тысячи, то да. Если больше, то надо перекладывать работу на мусю и оптимизировать базу под задачу.

Но

SELECT `id`,MAX(name), MIN(name) FROM `sort_by_name` GROUP BY `id`;

это тоже не подходит для большого количества строк, как и временная таблица для выбора никому ненужной информации, которой даже на странице нет как таковой.

[BlackCat]

ну всё относительно, если записей меньше тысячи, то да. Если больше, то надо перекладывать работу на мусю и оптимизировать базу под задачу.

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

Но

SELECT `id`,MAX(name), MIN(name) FROM `sort_by_name` GROUP BY `id`;

это тоже не подходит для большого количества строк, как и временная таблица для выбора никому ненужной информации, которой даже на странице нет как таковой.

С этим то же почти согласен. "Почти" - потому, что другого способа выполнить ту же операцию нет (или он мне неизвестен).

[MAK]

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

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

[BlackCat]

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

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

[ADRE]

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