Форум dkLab и Denwer
Здесь общаются Web-разработчики.
Генеральный спонсор:
Хостинг «Джино»

Элегантные решения повседневных задач (Константин Жинько [tIT], оценка: 5)
Author Message
Константин Жинько [tIT]
Сотрудник «Лаборатории»



Joined: 12 Jun 2004
Posts: 2264
Карма: 105
   поощрить/наказать

Location: Москва

PostPosted: Thu Sep 27, 2007 4:26 pm (написано за 4 минуты 3 секунды)
   Post subject: Элегантные решения повседневных задач
Reply with quote

Этой темой я хочу начать описывать интересные решения для задач, которые повседневно решаются в боевых условиях.
Своего рода сборник HOWTO по SQL. Помимо самого решения я также буду приводить достаточно подробное его описание.
Если у вас возникнет вопрос или комментарий, заведите отдельную тему по конкретному решению, если до вас это не сделал кто-то другой.
Просьба не писать сюда ничего, кроме задач и их решений в формате:

1. Постановка задачи
2. Решение
3. Объяснение
Back to top
View user's profile Send private message
Константин Жинько [tIT]
Сотрудник «Лаборатории»



Joined: 12 Jun 2004
Posts: 2264
Карма: 105
   поощрить/наказать

Location: Москва

PostPosted: Thu Sep 27, 2007 8:00 pm (спустя 3 часа 33 минуты; написано за 10 минут 48 секунд)
   Post subject: Задача №1
Reply with quote

Задача:

Одна из самых распространенных задач в Web'е - реализация постраничной навигации на сайте. Но данная задача чуток сложнее.

Пусть у нас есть некоторый хронологический справочник, вроде ленты новостей. Для простоты возьмем два поля:
Code (SQL): скопировать код в буфер обмена
CREATE TABLE news_list (
        news_date TIMESTAMP NOT NULL,
        news_title VARCHAR(255)
);
Как хранится контент - дело десятое. Задача: на странице конкретной новости сделать навигацию вроде "предыдущая новость/следующая новость", но количество ссылок должно быть больше - не более трех до и не более трех после, причем количество ссылок постоянно, т.е. если мы читаем самую свежую новость, то выводится шесть предыдущих и т.д.
Стандартное решение: послать два запроса, отсортировав данные в каждом из которых по дате, добавив ограничение на количество (в данном случае 2х3 = 6) и срез по дате - больше текущей и меньше текущей. Ну и потом уже объединить эти два набора через какой-нибудь разделитель и определить сколько записей и с какой стороны обрезать.

Возникает вопрос, а можно ли как-нибудь обойтись одним запросом, в котором получить все необходимые данные?
Можно.

Решение:
Code (SQL): скопировать код в буфер обмена
        SELECT 0 AS news_date, 'DUMMY' AS news_title
UNION
(
        SELECT
                UNIX_TIMESTAMP(nl_1.news_date) AS news_date,
                nl_1.news_title
        FROM news_list nl_1
        WHERE UNIX_TIMESTAMP(nl_1.news_date) > ?d
        ORDER BY news_date ASC
        LIMIT 0, 6 -- 3x2 = 6
)
UNION
        SELECT ?d AS news_date, 'CURRENT' AS news_title
UNION
(
        SELECT
                UNIX_TIMESTAMP(nl_2.news_date) AS news_date,
                nl_2.news_title
        FROM news_list nl_2
        WHERE UNIX_TIMESTAMP(nl_2.news_date) < ?d
        ORDER BY news_date DESC
        LIMIT 0, 6 -- 3x2 = 6
)
ORDER BY news_date DESC
Объяснение:

В стандарте SQL сказано, что нельзя использовать в запросе ORDER BY перед UNION. И действительно нельзя так делать в первом предложении SELECT, но можно во всех остальных (MySQL во всяком случае такую возможность предоставляет). Грех не воспользоваться, поэтому первое предложение SELECT в нашем запросе не несет никакой смысловой нагрузки и мы в качестве значения для даты выставляем ноль, чтобы эта запись была гарантировано первой или последней, в зависимости от результирующей сортировки. Далее, мы в скобках берем первые 6 записей, которые новее заданной, для этого сортируем по cdate ASC (ведь нам нужны те записи, которые идут СРАЗУ ЖЕ после заданной, а не любые более новые). Следующее предложение SELECT служит для разделителя записей до и записей после; чтобы общий результат корректно сортировался, мы задаем реальную дату через плэйсхолдер. Ну последнее предложение SELECT выбирает 6 более старых записей, идущих сразу же после заданной, для чего сортируем по DESC. Последняя приставка ORDER BY действует на весь результирующий набор - без нее данные будут разбиты на отсортированные в разных порядках наборы. В клиентской программе не забываем удалить нашу DUMMY-запись, прежде чем обрабатывать полученные данные.

Пример не учебный, используется на сайте newtimes.ru/
Запрос отрабатывает за ~240ms, учитывая всякие жуткие джойны и то, что сама СУБД никоим образом не оптимизирована.
Back to top
View user's profile Send private message
Rin
Участник форума



Joined: 01 Jun 2005
Posts: 515
Карма: 182
   поощрить/наказать

Location: Москва

PostPosted: Fri Sep 28, 2007 10:51 am (спустя 14 часов 50 минут; написано за 10 минут 16 секунд)
   Post subject:
Reply with quote

Пора заводить раздел "Склад готовых решений :: SQL" :)

Last edited by Rin on Fri Oct 03, 2008 10:45 am; edited 8 times in total
Back to top
View user's profile Send private message Send e-mail
Константин Жинько [tIT]
Сотрудник «Лаборатории»



Joined: 12 Jun 2004
Posts: 2264
Карма: 105
   поощрить/наказать

Location: Москва

PostPosted: Fri Sep 28, 2007 11:09 am (спустя 18 минут; написано за 17 минут 47 секунд)
   Post subject: Частный случай задачи №1
Reply with quote

Задача:

Рассмотрим частный случай задачи №1.
Пусть данные в базу гарантировано подаются в постоянной дельтой во времени (каждые пять минут, каждый день, каждый год - не важно).
Тогда задача упрощается до одного предложения SELECT.

Решение:
Code (SQL): скопировать код в буфер обмена
SELECT
        ABS(UNIX_TIMESTAMP(nl.news_date) - ?d) AS sort_field,
        UNIX_TIMESTAMP(nl.news_date) AS news_date,
        nl.news_title
FROM news_list nl
ORDER BY sort_field
LIMIT 0,7
Объяснение:

Плэйсхолдер в запросе заменяется датой текущей новости и после сортировки по абсолютному значению разности дат мы получаем интересный набор (пусть дельта дат равна минуте):
Quote:
первая запись является текущей
вторая - раньше или позже на минуту
третья - раньше или позже на минуту
четвертая - раньше или позже на две минуты
пятая - раньше или позже на две минуты
шестая - раньше или позже на три минуты
седьмая - раньше или позже на три минуты
Дальше в цикле мы перебираем этот набор и выталкиваем каждую следующую запись в начало или конец массива в зависимости от знака разности даты текущей новости и даты обрабатываемой в цикле записи.

Если не очевидно, почему этот замечательный способ не сработает в общем случае, рассмотрим такие даты за 28.09.2007:
Quote:
14:00; 16:00; 16:05; 16:10; 16:15; 16:20; 16:25; 16:30;
16:35; 16:40; 16:45; 16:50; 16:55; 17:00; 17:05; 17:10;
17:15; 17:20; 17:25; 17:30; 17:35; 17:40; 17:45; 17:50;
17:55; 18:00
Вопрос на засыпку: будет ли в первых семи записях новость с датой 14:00, если дата текущей новости - 16:00?
Правильно, не будет. И это у меня еще красивые данные - в жизни все намного хуже (-;

P.S. Это решение мне вчера вечером предложил Глеб Белокрыс, за что ему спасибо.
Back to top
View user's profile Send private message
Rin
Участник форума



Joined: 01 Jun 2005
Posts: 515
Карма: 182
   поощрить/наказать

Location: Москва

PostPosted: Fri Oct 26, 2007 3:09 pm (спустя 28 дней 4 часа 4 секунды; написано за 7 минут 44 секунды)
   Post subject:
Reply with quote

Задача

Как сделать в MySQL уникальный ключ на поле типа BLOB или TEXT, чтобы избежать дублирования записей?

Решение

DROP TABLE IF EXISTS `test`.`t`;
CREATE TABLE `test`.`t` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `content` text NOT NULL,
  `content_md5` varchar(32) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `content_md5` (`content_md5`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO t SET content = 'very big text', content_md5 = MD5(content);

Объяснение

Вставка данных в таблицу одновременно втавляет его md5 хеш, на который стоит уникальный ключ. :)
Причем порядок перечисления полей важен.

P.S.
Не понятно, почему такой простой способ разработчики MySQL не встроили в БД.
В случае MD5 хеш коллизий (что маловероятно), можно использовать более надежную хеш-функцию SHA1().
Back to top
View user's profile Send private message Send e-mail
Rin
Участник форума



Joined: 01 Jun 2005
Posts: 515
Карма: 182
   поощрить/наказать

Location: Москва

PostPosted: Thu Oct 16, 2008 10:15 am (спустя 11 месяцев 20 дней 19 часов 5 минут; написано за 51 секунду)
   Post subject:
Reply with quote

Склад готовых решений для MySQL >= 5.x.x

www.artfulsoftware.com/infotree/queries.php?&bw=1144
Back to top
View user's profile Send private message Send e-mail
Никитин Максим
Заглянувший



Joined: 13 Jan 2009
Posts: 7
Карма: 1
   поощрить/наказать


PostPosted: Fri Jan 30, 2009 7:33 pm (спустя 3 месяца 14 дней 9 часов 18 минут; написано за 20 минут 27 секунд)
   Post subject: Re: Задача №1
Reply with quote

Константин Жинько [tIT] wrote:
Задача:

Одна из самых распространенных задач в Web'е - реализация постраничной навигации на сайте. Но данная задача чуток сложнее.

Пусть у нас есть некоторый хронологический справочник, вроде ленты новостей. Для простоты возьмем два поля:
Code (SQL): скопировать код в буфер обмена
CREATE TABLE news_list (
        news_date TIMESTAMP NOT NULL,
        news_title VARCHAR(255)
);
Если у нас гарантированно дата новости отличается хотя бы на секунду, то можно сделать так:
Code (SQL): скопировать код в буфер обмена
SELECT l.news_date, l.news_title
FROM news_list l
WHERE (l.news_date IN (
                SELECT l2.news_date
                FROM news_list l2
                WHERE l2.news_date < $CUR_DATE
                ORDER BY l2.news_date DESC LIMIT 0, 3)
        ) OR (l.news_date >= $CUR_DATE)
ORDER BY l.news_date ASC LIMIT 0, 7
Где $CUR_DATE - дата текущей новости.
Мне кажется, это решение лучше, чем с юнионами - по крайней мере, всего два запроса. Плюс, оно не зависит от периодичности выхода новостей. Лишь бы не было одинаковых news_date до секунды, а это, я думаю, в жизни именно так.
Back to top
View user's profile Send private message Send e-mail
Никитин Максим
Заглянувший



Joined: 13 Jan 2009
Posts: 7
Карма: 1
   поощрить/наказать


PostPosted: Mon Feb 02, 2009 3:26 pm (спустя 2 дня 19 часов 52 минуты; написано за 1 минуту 59 секунд)
   Post subject:
Reply with quote

Кстати, я тут подумал на досуге :) Мое решение будет работать даже в том случае, если у новостей одинаковые даты с точностью до секунды. Просто скрипт не сможет точно определить, какая новость должна быть раньше, а какая позже и их сортировка друг относительно друга будет, грубо говоря, случайна. А в остальном все будет работать.
Back to top
View user's profile Send private message Send e-mail
dimagolov
Участник форума



Joined: 04 Feb 2007
Posts: 1664
Карма: 96
   поощрить/наказать

Location: Christ Church, Barbados

PostPosted: Mon Feb 02, 2009 6:48 pm (спустя 3 часа 22 минуты; написано за 1 минуту 56 секунд)
   Post subject:
Reply with quote

Никитин Максим, а вот мне кажется, что запросов будет столько, сколько строк в таблице news_list, потому что подзапрос будет выполняться для каждой строки проверяемой по WHERE. Посмотрите EXPLAIN

Кроме того, это тема для выкладывания готовых и оттестированных решений, а не рассуждений на тему "я подумал"
Back to top
View user's profile Send private message
Никитин Максим
Заглянувший



Joined: 13 Jan 2009
Posts: 7
Карма: 1
   поощрить/наказать


PostPosted: Mon Feb 02, 2009 11:56 pm (спустя 5 часов 7 минут; написано за 2 минуты 55 секунд)
   Post subject:
Reply with quote

dimagolov wrote:
Кроме того, это тема для выкладывания готовых и оттестированных решений, а не рассуждений на тему "я подумал"
Извиняюсь. Просьба к модераторам отделить лишние сообщения в новую тему.
Back to top
View user's profile Send private message Send e-mail
Константин Жинько [tIT]
Сотрудник «Лаборатории»



Joined: 12 Jun 2004
Posts: 2264
Карма: 105
   поощрить/наказать

Location: Москва

PostPosted: Tue Apr 14, 2009 12:40 pm (спустя 2 месяца 11 дней 12 часов 44 минуты; написано за 1 час 30 минут 57 секунд)
   Post subject: tsearch2 рулит или ускорение поиска пользователей
Reply with quote

PostgreSQL 8.3.x
Имеется сложный и медленный запрос внутри хранимой процедуры, не поддающийся оптимизации в силу своей природы:
Code (SQL): скопировать код в буфер обмена
EXPLAIN SELECT ...
FROM "user" u
LEFT JOIN user_profile p ON p.user_id = u.id
LEFT JOIN company_stuff cs ON cs.user_id = u.id
LEFT JOIN company c ON c.id = cs.company_id
WHERE        c.name LIKE $1
OR        u.login LIKE $1
OR        p.first_name LIKE $1
OR        p.last_name LIKE $1
Quote:
Hash Left Join (cost=20190.35..39726.87 rows=49 width=8)
  Hash Cond: (u.id = cs.user_id)
  Filter: (((c.name)::text ~~ '%kzhinko%'::text) OR ((u.login)::text ~~ '%kzhinko%'::text) OR ((p.firstname)::text ~~ '%kzhinko%'::text) OR ((p.lastname)::text ~~ '%kzhinko%'::text))
  -> Hash Left Join (cost=10285.44..25439.64 rows=252487 width=29)
        Hash Cond: (u.id = p.user_id)
        -> Seq Scan on "user" u (cost=0.00..10002.87 rows=252487 width=16)
        -> Hash (cost=7396.86..7396.86 rows=231086 width=21)
              -> Seq Scan on user_profile p (cost=0.00..7396.86 rows=231086 width=21)
  -> Hash (cost=8744.01..8744.01 rows=92872 width=25)
        -> Hash Left Join (cost=5365.85..8744.01 rows=92872 width=25)
              Hash Cond: (cs.company_id = c.id)
              -> Seq Scan on company_stuff cs (cost=0.00..1520.72 rows=92872 width=16)
              -> Hash (cost=4171.49..4171.49 rows=95549 width=25)
                    -> Seq Scan on company c (cost=0.00..4171.49 rows=95549 width=25)
В этом запросе две основных жести:

1. Условие для LIKE непредсказуемо и индекс по нему не построишь.
2. Ни один индекс не задействован при объединении таблиц (в основном из-за отсутствия LIMIT и/или WHERE с ограничением тех же u.id).

Задача: переделать все на хрен, чтобы летало.


Основные идеи:

1. Поиск в таблицах и добыча данных для найденных пользователей - суть разные вещи. Если научимся искать ID пользователей и подсовывать их в выборку данных, все индексы будут задействованы.
2. Поскольку поиск разделен по OR, то он для каждой из таблиц независим.
3. Если не LIKE, то что? Очевидно, полнотекстовый поиск по таблицам. В нашем случае tsearch2.


Соберем и установим tsearch2 из контриба и навешаем на наши таблицы поля с индексами, забьем их данными и навешаем триггеры:
Code (SQL): скопировать код в буфер обмена
-- в таблице user ищем только по логину:
ALTER TABLE "user" ADD COLUMN fts tsvector;
CREATE INDEX user_fts_ix ON "user" USING gist(fts);
UPDATE "user" SET fts = setweight(coalesce(to_tsvector('russian', "login"), ''), 'A');
CREATE OR REPLACE FUNCTION tr_user_fts() RETURNS TRIGGER AS
$$
DECLARE
        _need_update BOOLEAN := true;
BEGIN
        IF TG_OP = 'UPDATE' THEN
                IF ROW(NEW.login) IS NOT DISTINCT FROM ROW(OLD.login) THEN
                        _need_update := false;
                END IF;
        END IF;
        IF _need_update THEN
                NEW.fts := setweight(coalesce(to_tsvector('russian', NEW.login), ''), 'A');
        END IF;
END;
$$
LANGUAGE plpgsql VOLATILE;
CREATE TRIGGER biu_tr_user_fts
        BEFORE INSERT OR UPDATE
        ON "user"
        FOR EACH ROW
        EXECUTE tr_user_fts();

-- в таблице user_profile ищем по ФИО:
ALTER TABLE user_profile ADD COLUMN fts tsvector;
CREATE INDEX user_profile_fts_ix ON user_profile USING gist(fts);
UPDATE user_profile SET fts =        setweight(coalesce(to_tsvector('russian', firstname), ''), 'A') ||
                                setweight(coalesce(to_tsvector('russian', middlename), ''), 'A') ||
                                setweight(coalesce(to_tsvector('russian', lastname), ''), 'A');
CREATE OR REPLACE FUNCTION tr_user_profile_fts() RETURNS TRIGGER AS
$$
DECLARE
        _need_update BOOLEAN := true;
BEGIN
        IF TG_OP = 'UPDATE' THEN
                IF ROW(NEW.firstname, NEW.middlename, NEW.lastname) IS NOT DISTINCT FROM ROW(OLD.firstname, OLD.middlename, OLD.lastname) THEN
                        _need_update := false;
                END IF;
        END IF;
        IF _need_update THEN
                NEW.fts :=        setweight(coalesce(to_tsvector('russian', firstname), ''), 'A') ||
                                setweight(coalesce(to_tsvector('russian', middlename), ''), 'A') ||
                                setweight(coalesce(to_tsvector('russian', lastname), ''), 'A');
        END IF;
END;
$$
LANGUAGE plpgsql VOLATILE;
CREATE TRIGGER biu_tr_user_profile_fts
        BEFORE INSERT OR UPDATE
        ON user_profile
        FOR EACH ROW
        EXECUTE tr_user_profile_fts();

-- в таблице company ищем по краткому или полному названию компании:
ALTER TABLE company ADD COLUMN fts tsvector;
CREATE INDEX company_fts_ix ON company USING gist(fts);
UPDATE company SET fts =        setweight(coalesce(to_tsvector('russian', "name"), ''), 'A') ||
                                setweight(coalesce(to_tsvector('russian', full_name), ''), 'A');
CREATE OR REPLACE FUNCTION tr_company_fts() RETURNS TRIGGER AS
$$
DECLARE
        _need_update BOOLEAN := true;
BEGIN
        IF TG_OP = 'UPDATE' THEN
                IF ROW(NEW.name, NEW.full_name) IS NOT DISTINCT FROM ROW(OLD.name, OLD.full_name) THEN
                        _need_update := false;
                END IF;
        END IF;
        IF _need_update THEN
                NEW.fts :=        setweight(coalesce(to_tsvector('russian', "name"), ''), 'A') ||
                                setweight(coalesce(to_tsvector('russian', full_name), ''), 'A');
        END IF;
END;
$$
LANGUAGE plpgsql VOLATILE;
CREATE TRIGGER biu_tr_company_fts
        BEFORE INSERT OR UPDATE
        ON company
        FOR EACH ROW
        EXECUTE tr_company_fts();
Комментарии:

1. В plpgsql условные выражения вычисляются всегда, поэтому при IF TG_OP = 'UPDATE' AND ROW(NEW.*) IS NOT DISTINCT FROM ROW(OLD.*) в случае добавления в таблицу триггер отвалится (а вместе с ним и вставка в таблицу), поскольку при INSERT константа OLD не определена.
2. setweight($1, $2) задает поисковый ранг $2 для строки-вектора $1; coalesce($1, $2, $3, ...) возвращает первый из своих аргументов, не являющийся NULL'ом; to_tsvector($1, $2) возвращает строку-вектор для строки $2 в соответствии со словарем $1.
3. Выражение $1 IS NOT DISTINCT FROM $2, где $1 и $2 - наборы данных одного композитного типа возвращает истину, если $1 и $2 эквивалентны, причем, работает быстрее, чем последовательное сравнение полей $1 и $2 (см. www.postgres.cz/index.php/PostgreSQL_SQL_Tricks#Fast_compare_variables_NEW_and_OLD_in_trigger.27s_body).

Теперь в каждой из этих таблиц можно искать следующим образом:
Code (SQL): скопировать код в буфер обмена
EXPLAIN
SELECT id
FROM user_profile
WHERE fts @@ to_tsquery('russian', quote_literal('search string'))
Quote:
Bitmap Heap Scan on user_profile (cost=10.08..774.05 rows=231 width=8)
  Filter: (fts @@ '''search'' & ''string'''::tsquery)
  -> Bitmap Index Scan on user_profile_fts_ix (cost=0.00..10.02 rows=231 width=0)
        Index Cond: (fts @@ '''search'' & ''string'''::tsquery)
Красота! А еще можно сортировать результаты по релевантности:
Code (SQL): скопировать код в буфер обмена
EXPLAIN
SELECT
        id,
        ts_rank(fts, q) AS rank
FROM
        user_profile p,
        plainto_tsquery('russian', quote_literal('search string')) q
WHERE fts @@ q
ORDER BY rank DESC
Quote:
Sort (cost=786.01..786.59 rows=231 width=77)
  Sort Key: (ts_rank(p.fts, q.q))
  -> Nested Loop (cost=10.08..776.95 rows=231 width=77)
        -> Function Scan on q (cost=0.00..0.01 rows=1 width=32)
        -> Bitmap Heap Scan on user_profile p (cost=10.08..773.47 rows=231 width=45)
              Filter: (p.fts @@ q.q)
              -> Bitmap Index Scan on user_profile_fts_ix (cost=0.00..10.02 rows=231 width=0)
                    Index Cond: (p.fts @@ q.q)
Теперь у нас есть два пути: решать задачу в лоб или подумать о повторном использовании кода. Я выбираю второй, о котором и рассказываю.
Создадим функцию, которая облегчит поиск в таблице:
Code (SQL): скопировать код в буфер обмена
CREATE TYPE t_search_rank AS (
        id bigint,
        rank float4
);
CREATE OR REPLACE FUNCTION simple_table_search(_table, _search_strin) RETURNS t_search_rank[] AS
$$
DECLARE
        _buf t_search_rank;
        _found t_search_rank[];
        _i smallint;
BEGIN
        _i := 0;
        FOR _buf IN EXECUTE
                'SELECT
                        id,
                        ts_rank(fts, q) AS rank
                FROM
                        "'
|| _table || '",
                        plainto_tsquery('
'russian'', ' || quote_literal(_search_string) || ') q
                WHERE fts @@ q'

        LOOP
                _found[_i] :=_buf;
                _i := _i + 1;
        END LOOP;
        RETURN _found;
END;
$$
LANGUAGE plpgsql STABLE;
Эта процедура возвращает нам все найденные ID'ы записей с их ранками в виде массива. Подразумевается, что в таблице, заданной для поиска есть поля id типа bigint и fts типа tsvector.

В нашей задаче требуется искать сразу в нескольких таблицах, причем нас интересуют только ID'ы пользователей на выходе. Добавим процедуру, которая решает эту задачу:
Code (SQL): скопировать код в буфер обмена
CREATE OR REPLACE FUNCTION user_search(_search_string text) RETURNS SETOF t_search_rank AS
$$
SELECT
        found.id,
        SUM(found.rank) AS sum_rank
FROM(
        -- search in user by login
        SELECT *
        FROM array_unpack(simple_table_search('user', $1))

        UNION

        -- search in user_profile by {first|middle|last}name
        SELECT o.user_id, s.rank
        FROM (
                SELECT p.user_id, p.id
                FROM user_profile p
                WHERE p.id IN (SELECT id FROM array_unpack(simple_table_search('user_profile', $1)))
        ) o,
        (SELECT * FROM array_unpack(simple_table_search('user_profile', $1))) s
        WHERE s.id = o.id

        UNION

        -- search in company by name, full_name
        SELECT o.user_id, s.rank
        FROM (
                SELECT cs.user_id, c.id
                FROM        company c,
                        company_stuff cs
                WHERE c.id IN (SELECT id FROM array_unpack(simple_table_search('company', $1)))
                AND        cs.company_id = c.id
        ) o,
        (SELECT * FROM array_unpack(simple_table_search('company', $1))) s
        WHERE s.id = o.id
) found
GROUP BY id;
$$
LANGUAGE 'sql' STABLE;
Здесь уже возвращаются найденные юзерские ID'ы и суммарный ранг. Суммирование ранга здесь весьма топорный вариант, но мы все же не гугль-бум пишем.

Комментарии:

1. Активно юзается процедура array_unpack, которая представляет любой массив в виде таблицы (см. www.postgres.cz/index.php/PostgreSQL_SQL_Tricks#Array_to_table).
2. В последнем запросе из union simple_table_search хоть и дергается два раза, но реально работает только один раз, поскольку объявлена, как stable (в рамках одной транзакции процедуры, объявленные как stable на одних и тех же входных данных будут отдавать одни и те же выходные, независимо от состояния базы, то бишь результат кэшируется).

Теперь запрос
Code (SQL): скопировать код в буфер обмена
EXPLAIN
SELECT ...
FROM
        "user" u
LEFT JOIN user_profile p ON p.user_id = u.id
LEFT JOIN company_stuff cs ON cs.user_id = u.id
LEFT JOIN company c ON cs.company_id = c.id
WHERE u.id IN (SELECT id FROM user_search('search string'))
показывает уже совсем другую картину
Quote:
Nested Loop Left Join (cost=262.50..2203.08 rows=200 width=8)
  -> Nested Loop Left Join (cost=262.50..1955.01 rows=200 width=8)
        -> Nested Loop Left Join (cost=262.50..1810.34 rows=200 width=16)
              -> Nested Loop (cost=262.50..1729.52 rows=200 width=8)
                    -> HashAggregate (cost=262.50..264.50 rows=200 width=8)
                          -> Function Scan on search (cost=0.00..260.00 rows=1000 width=8)
                    -> Index Scan using pk_user_id on "user" u (cost=0.00..7.31 rows=1 width=8)
                          Index Cond: (u.id = search.id)
              -> Index Scan using ix_company_stuff_user_id on company_stuff cs (cost=0.00..0.39 rows=1 width=16)
                    Index Cond: (cs.user_id = u.id)
        -> Index Scan using pk_company_id on company c (cost=0.00..0.71 rows=1 width=8)
              Index Cond: (cs.company_id = c.id)
  -> Index Scan using ix_user_profile_user_id on user_profile p (cost=0.00..1.23 rows=1 width=8)
        Index Cond: (p.user_id = u.id)
Но это только выборка данных, а нам еще нужно их отсортировать по рангу, для чего пишем следующую хранимую процедуру (в которой собственно и был изначальна супер-тяжелый запрос):
Code (SQL): скопировать код в буфер обмена
CREATE OR REPLACE FUNCTION user_list(_search_string character varying) RETURNS SETOF t_user_info AS
$$
DECLARE
        _found t_user_info[];
        _row t_user_info;
BEGIN
        FOR _row IN
                SELECT ...
                FROM
                        "user" u
                LEFT JOIN user_profile p ON p.user_id = u.id
                LEFT JOIN company_stuff cs ON cs.user_id = u.id
                LEFT JOIN company c ON cs.company_id = c.id
                WHERE u.id IN (SELECT id FROM user_search(_search_string))
        LOOP
                _found := array_append(_found, _row);
        END LOOP;

        FOR _row IN
                SELECT f.*
                FROM
                        user_search(_search_string) s,
                        array_unpack(_found) f
                WHERE s.id = f.user_id
                ORDER BY s.rank DESC
        LOOP
                RETURN NEXT _row;
        END LOOP;
END;
$$
LANGUAGE 'plpgsql' STABLE;
Здесь мы результат выборки пользователей засунули в массив _found, которые затем сджойнили опять с результами поиска user_search по user_id, отсортировали по рангу и отдали на выход. Поскольку, все испольуемые внутри процедуры объявлены, как stable, никаких накладных расходов не имеется.

Итого, если раньше моя учетная запись по логину искалась по старому запросу две секунды, то сейчас в среднем 15ms. Персонаж Вася ищется уже не 15-20 секунд, а порядка 100-200ms. Скорость выполнения запроса увеличилась на два порядка, что очень даже неплохо, я считаю.

Бонусы от повторного использования:

1. Появилась замечательная процедура simple_table_search, инкапуслирующая поиск в таблице.
2. Чтобы искать в нескольких таблицах, можно писать обертки (например, user_search, ищущий сразу в трех таблицах, но выдающий только пользовательские id'ы).
3. Можно делать сложные выборки с помощью ограничения user_search, так, чтобы использовались индексы, чего не добиться (или добиться, но очень мутным и нетривиальным способом) при выборке в лоб.
4. Чтобы узнать количество найденных записей, все данные не нужны - лучше использовать для этого обертки.


Замечание: словари конечно надо настраивать; russian прекрасно разбирается в русской морфологии, но до него не доходит, что "василий" и "вася" одно и то же. Более того у "васи" он найдет корень "ва", поэтому строка "вавава" будет иметь более высокий ранг, чем строка "вася", что конечно неправильно.
Back to top
View user's profile Send private message
KES
Участник форума



Joined: 19 Oct 2006
Posts: 214
Карма: 0
   поощрить/наказать

Location: сВознесеновка, Мелитопольский р-н, Запорожская обл. Украина

PostPosted: Tue Apr 06, 2010 11:51 pm (спустя 11 месяцев 22 дня 11 часов 10 минут; написано за 5 минут 23 секунды)
   Post subject:
Reply with quote

1. Постановка задачи
Выбрать ИД людей с максимальным возрастом в группе по фамилии
Code (any language): скопировать код в буфер обмена
nm      fam     year
angi    ali     20
angi    berly   30
cor     ali     40
2.
Code (SQL): скопировать код в буфер обмена
SELECT
 nm
 ,fam
 ,max(year)
FROM test
GROUP BY fam
Ожидаемый результат:
Code (any language): скопировать код в буфер обмена
angi    berly   30
cor     ali     40
Есть ли элегантный запрос для решения этой задачи в FireBird?

Last edited by KES on Wed Apr 07, 2010 10:12 am; edited 4 times in total
Back to top
View user's profile Send private message
dimagolov
Участник форума



Joined: 04 Feb 2007
Posts: 1664
Карма: 96
   поощрить/наказать

Location: Christ Church, Barbados

PostPosted: Wed Apr 07, 2010 12:51 am (спустя 1 час 38 секунд; написано за 2 минуты 13 секунд)
   Post subject:
Reply with quote

Code (SQL): скопировать код в буфер обмена
SELECT group_concat(id)
FROM test_age
GROUP BY age
ORDER BY age DESC
LIMIT 1
только я не до конца понял фразы
Quote:
Выбрать ИД людей с максимальным возрастом в группе по фамилии
то есть начало понял, а при чем "по фамилии" нет. Хотя, если вопрос про однофамильцев, то where lastName = LName поможет.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic All times are GMT + 3 Hours
Page 1 of 1    Email to a Friend.
You cannot post new topics in this forum. You cannot reply to topics in this forum. You cannot edit your posts in this forum. You cannot delete your posts in this forum. You cannot vote in polls in this forum. You cannot attach files in this forum. You can download files in this forum.
XML