Форум пользователей MySQL

НУ да! Т.е. чтобы можно было сделать пейджинг и листать по 2 группы!

Как говорят японцы: "Прошу простить мне мое слабоумие", но...


Вот есть две таблицы - g (Группы) и p (Потомки).

DROP TABLE IF EXISTS `test`.`g`;
CREATE TABLE  `test`.`g` (
  `x` int(4) unsigned default NULL
) ENGINE=MyISAM;
insert into `test`.`g` values(3),(2),(4),(5),(1);

DROP TABLE IF EXISTS `test`.`p`;
CREATE TABLE  `test`.`p` (
  `y` int(4) unsigned default NULL,
  `data` int(4) unsigned default NULL
) ENGINE=MyISAM;
insert into `test`.`p` values
(3,172),(3,721),(3,652),(3,444),(3,781)
,(2,48),(2,27),(2,13),(2,16),(2,99)
,(4,4854),(4,2723),(4,1327),(4,1663),(4,9953)
,(5,48545),(5,27423),(5,12327),(5,16639),(5,99530)
,(1,4),(1,7),(1,3),(1,6),(1,9);

Выборка select * from g t1 join p t2 on t1.x=t2.y:

+------+------+-------+
| x    | y    | data  |
+------+------+-------+
|    3 |    3 |   172 |
|    3 |    3 |   721 |
|    3 |    3 |   652 |
|    3 |    3 |   444 |
|    3 |    3 |   781 |
|    2 |    2 |    48 |
|    2 |    2 |    27 |
|    2 |    2 |    13 |
|    2 |    2 |    16 |
|    2 |    2 |    99 |
|    4 |    4 |  4854 |
|    4 |    4 |  2723 |
|    4 |    4 |  1327 |
|    4 |    4 |  1663 |
|    4 |    4 |  9953 |
|    5 |    5 | 48545 |
|    5 |    5 | 27423 |
|    5 |    5 | 12327 |
|    5 |    5 | 16639 |
|    5 |    5 | 99530 |
|    1 |    1 |     4 |
|    1 |    1 |     7 |
|    1 |    1 |     3 |
|    1 |    1 |     6 |
|    1 |    1 |     9 |
+------+------+-------+
25 rows in set (0.00 sec)

То есть 3,2,4,5,1 - номера групп.

vasya написал:

Нужно поменять порядок условий в части where (я просто писал по смыслу без проверки).
Если сначала идет @i<@n, то при невыполнении этого условия второе не проверяется (так как они идут через and). А нам нужно, чтобы наша конструкция с if выполнялась над каждой строкой.

Поэтому должно быть:
WHERE if(@p=x, @i:=@i+1,(@i:=0) or (@p:=x)) and @i<@n;

Пробую сделать как указано - поставить @i<@n после условия if, получается запрос:

select * from g t1 join p t2 on t1.x=t2.y  WHERE if (@p=x, @i:=@i+1,(@i:=0) or (@p:=x)) and @i<@n;

mysql> set @n=3, @i=0, @p=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from g t1 join p t2 on t1.x=t2.y  WHERE if (@p=x, @i:=@i+1,(@i:=0) or (@p:=x)) and @i<@n;
+------+------+------+
| x    | y    | data |
+------+------+------+
|    3 |    3 |  172 |
|    3 |    3 |  721 |
|    3 |    3 |  652 |
+------+------+------+
3 rows in set (0.00 sec)

То есть он выдал три элемента с первой группы, но ведь всего групп 5 штук!
Почему он выдал только с первой?

Приветствую, paulus :-), cпасибо за ответ!

paulus написал:

А можно я поучаствую, да?

Будем польщены!

paulus написал:

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

Ммм, речь идёт о том, что лучше сперва получить перечень id-шников групп (допустим простым select'ом) и потом уже серией запросов из каждой группы получать по три (или сколько нужно элементов) допустим вот таким запросом (select data from p where p.y = ВЫШЕПОЛУЧЕННЫЙID_ГРУППЫ limit 3 )? Конечно при  наличии индекса на p.y. Я правильно Вас понял?

Задача у меня стоит немножко другая, я ее в другом посте (http://sqlinfo.ru/forum/viewtopic.php?id=3321) озвучивал, просто задача текущего поста - часть к решению той, поэтому и пытаюсь разобраться досконально.

Что касается скорости, то в моей таблице не так много данных - несколько сотен максимум, поэтому тут оптимизация не столь острый камень.

paulus написал:

Код:

[silentia] root test > set @n=3, @i=0, @p=0; select *, if (@p=x, @i:=@i+1,(@i:=0) or (@p:=x)) and (@i<@n) f from g t1 join p t2 on t1.x=t2.y;
Query OK, 0 rows affected (0.00 sec)

+------+------+-------+------+
| x    | y    | data  | f    |
+------+------+-------+------+
|    3 |    3 |   172 |    1 |
|    3 |    3 |   721 |    1 |
|    3 |    3 |   652 |    1 |
|    3 |    3 |   444 |    0 |
|    3 |    3 |   781 |    0 |
|    2 |    2 |    48 |    1 |
|    2 |    2 |    27 |    1 |
|    2 |    2 |    13 |    1 |
|    2 |    2 |    16 |    0 |
|    2 |    2 |    99 |    0 |
|    4 |    4 |  4854 |    1 |
|    4 |    4 |  2723 |    1 |
|    4 |    4 |  1327 |    1 |
|    4 |    4 |  1663 |    0 |
|    4 |    4 |  9953 |    0 |
|    5 |    5 | 48545 |    1 |
|    5 |    5 | 27423 |    1 |
|    5 |    5 | 12327 |    1 |
|    5 |    5 | 16639 |    0 |
|    5 |    5 | 99530 |    0 |
|    1 |    1 |     4 |    1 |
|    1 |    1 |     7 |    1 |
|    1 |    1 |     3 |    1 |
|    1 |    1 |     6 |    0 |
|    1 |    1 |     9 |    0 |
+------+------+-------+------+
25 rows in set (0.00 sec)

Тут мы видим, что Вася написал нормальный код, который отлично работает
если он стоит в правильном месте.

А что является "правильным местом" ? Этот запрос выводит все элементы групп (а не по три, хотя в переменную n изначально было занесено значение 3). Такое ощущение, что условие and (@i<@n) просто выбрасывается при выполнении (результат запроса совпадает с результатом запроса set @n=3, @i=0, @p=0; select *, if (@p=x, @i:=@i+1,(@i:=0) or (@p:=x)) f from g t1 join p t2 on t1.x=t2.y;, а они отличаются только наличием/отсутствием and (@i<@n) ).

Хочу еще заметить, что в том варианте запроса, который я постил в прошлый раз
(select * from g t1 join p t2 on t1.x=t2.y  WHERE if (@p=x, @i:=@i+1,(@i:=0) or (@p:=x)) and @i<@n;)
вывелись только три строки, то есть как будто условие if перестало отрабатываться после того, как @i набрало нужное значение. Это объяснимо как-то?

paulus написал:

Осталось разобраться, чем отличается положение в списке полей от положения
в WHERE. Если быть совсем честным — я не знаю. Если быть не совсем честным,
то можно поглядеть на EXPLAIN: там написано, что WHERE применяется к обеим
табличкам. В каком порядке оно применяется — никому не известно (например,
если добавить индекс на p, то выводится по две строки каждой группы). То есть
на одну строку выборки IF может вызываться и ноль, и один, и даже два раза.

explain'ы вариантов запросов отличаются только наличием/отсутствием where на t1:

mysql> explain select *, if (@p=x, @i:=@i+1,(@i:=0) or (@p:=x)) and (@i<@n) f from g t1 join p t2 on t1.x=t2.y;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |    5 |             |
|  1 | SIMPLE      | t2    | ALL  | NULL          | NULL | NULL    | NULL |   25 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
2 rows in set (0.00 sec)

mysql> explain select * from g t1 join p t2 on t1.x=t2.y  WHERE if (@p=x, @i:=@i+1,(@i:=0) or (@p:=x)) and @i<@n;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |    5 | Using where |
|  1 | SIMPLE      | t2    | ALL  | NULL          | NULL | NULL    | NULL |   25 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
2 rows in set (0.01 sec)

paulus, а что имелось ввиду под если добавить индекс на p, то выводится по две строки каждой группы? p - это ж ведь просто пользовательская переменная, как на нее индекс добавишь?

Большое спасибо за наводку на читерское (нерекомендуемое Вами решение). Касательно having - здесь ведь нету группировки.
Если параметр GROUP BY в SELECT не задан, HAVING применяется к «группе» всех строк таблицы, полностью дублируя WHERE (допускается не во всех реализациях стандарта SQL). (c), а значит можно заменить having на where и результат получился такой же.

paulus написал:

Ээ... можно «на ты»? Всё-таки, знаем друг друга лично?

Конечно, сори , привык соблюдать официоз на форуме.

paulus написал:

В моем примере посмотри внимательно на значения f. Выборка получается тогда,
когда f = 1. Я просто перенес условие отбора из WHERE в список полей, чтобы
видеть, какие строки выбираются, а какие — отбрасываются.

Да, всё верно. Васины инструкции хорошо отрабатываются, когда находится в списке выбранных полей, а не в where. Но вообще с join'ом конструкция отрабатывается очень с большим скрипом - в конце-концов разве наличие индекса может поменять результат выполнения запроса (я именно про результат, а не выполнение)? А тут тесты дают след. результат:

Таблицы g и p непроиндексированы

mysql> set @n=3, @i=0, @p=0;  select * from g t1 join p t2 on t1.x=t2.y  WHERE if (@p=x, @i:=@i+1,(@i:=0) or (@p:=x)) and @i<@n;
Query OK, 0 rows affected (0.00 sec)

+------+------+------+
| x    | y    | data |
+------+------+------+
|    3 |    3 |  172 |
|    3 |    3 |  721 |
|    3 |    3 |  652 |
+------+------+------+
3 rows in set (0.00 sec)

Поле x таблицы g проиндексировано (таблица p непроиндексирована).

mysql> set @n=3, @i=0, @p=0;  select * from g t1 join p t2 on t1.x=t2.y  WHERE if (@p=x, @i:=@i+1,(@i:=0) or (@p:=x)) and @i<@n;
Empty set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

Поле y таблицы p проиндексировано (таблица g непроиндексирована).

mysql> set @n=3, @i=0, @p=0;  select * from g t1 join p t2 on t1.x=t2.y  WHERE if (@p=x, @i:=@i+1,(@i:=0) or (@p:=x)) and @i<@n;
Query OK, 0 rows affected (0.00 sec)

+------+------+-------+
| x    | y    | data  |
+------+------+-------+
|    3 |    3 |   172 |
|    3 |    3 |   721 |
|    2 |    2 |    48 |
|    2 |    2 |    27 |
|    4 |    4 |  4854 |
|    4 |    4 |  2723 |
|    5 |    5 | 48545 |
|    5 |    5 | 27423 |
|    1 |    1 |     4 |
|    1 |    1 |     7 |
+------+------+-------+
10 rows in set (0.00 sec)

Поле x таблицы g проиндексировано, поле y таблицы p проиндексировано.

mysql> set @n=3, @i=0, @p=0;  select * from g t1 join p t2 on t1.x=t2.y  WHERE if (@p=x, @i:=@i+1,(@i:=0) or (@p:=x)) and @i<@n;
Query OK, 0 rows affected (0.00 sec)

+------+------+-------+
| x    | y    | data  |
+------+------+-------+
|    1 |    1 |     4 |
|    1 |    1 |     7 |
|    2 |    2 |    48 |
|    2 |    2 |    27 |
|    3 |    3 |   172 |
|    3 |    3 |   721 |
|    4 |    4 |  4854 |
|    4 |    4 |  2723 |
|    5 |    5 | 48545 |
|    5 |    5 | 27423 |
+------+------+-------+
10 rows in set (0.01 sec)

Итого - 3 разных результата на 4 выборки.

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

Крайне интересно было бы услышать по этому посту соображения Васи (метод-то его), не известно ли тебе, Paulus, когда он на форуме появится? А то не видно его с конца января.

paulus написал:

Про WHERE и HAVING — тому ресурсу доверять не стоит Это почти никогда не
одно и то же.

Да, правда что . Спасибо!

Отредактированно deadka (14.02.2011 13:07:31)

Нашел решение. Может кому-то пригодится:

Отредактированно nexus (13.06.2011 23:19:34)

Таким образом проблему с оптимизатором можно обойти, подсунув ему выборку, на которую он сразу будет применять условие where, то есть что-то вроде

Отредактированно deadka (26.10.2011 01:37:47)