да, micscr, я даже не уверен, что мой вариант лучше. Но можешь попробовать сделать без подзапроса

удалено.

ага и это большая ошибка. без корреляции тут сложно обойтись, что я и привел в самом начале (micscr, твой запрос реализует тоже что и мой)
Эта идея будет долго работать.

удалено.

у тебя коррелированный подзапрос, а у меня - скалярный

твоя структура нарушает самый первый закон нормализации.

удалено.

а что там проверять, если база спроектирована неверно? Или ты не веришь? :blink: (пруфлинк я приводил) Ну тогда представь задачу: пользователь разбанивает одного своего забаненного. Твои действия.

p.s. если честно - не ожидал от тебя такого решения :(

покрутил, повертел - как то не вижу сходу варианта без подзапроса.

удалено.

Gozar, не очень тебя понял. Ведь это практическая задача, значит в конечном счете интересует производительность. И непонятно с какой стороны оценивать идею. Надо тогда реализацию за тебя додумывать. Можно только сказать, что скорее всего твой первый вариант был лучше. А задачу решить не обязательно...

завтра ближе к вечеру выложу свой вариант

удалено.

я тебя понял, не вопрос :)

Gozar, довольно предсказуемо рано или поздно сесть в лужу если говорить о вещах которые не понимаешь.

Программирование - это инженерная профессия к которой надо относиться с уважением, потому что если без уважения так:
то и программирование отнесется к тебе без уважения ;) и
через десять лет человек не будет знать даже элементарных основ работы с базами данных ... А это говорит почти о всем.

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

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

x-yuri, ну что показываешь как сделал? Больше никто не интересуется все равно.

Почему? Мне интересно как сделать запрос без сабзапроса. Инетересна структура таблиц прежде всего.

Вот еще вариант, уже без подзапроса:

удалено.

[offtop]
Опа опа. попкорн всегда при мне.
[/offtop]

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

drop table if exists users_2;
create table users_2 ( id int primary key );
insert into users_2 values (1), (2), (3), (4), (5), (6);
drop table if exists banned_3;
create table banned_3 (
    user_1_id int,
    user_2_id int,
    origin enum('1', '2'),
    primary key(user_1_id, user_2_id)
);
insert into banned_3 values
(2, 3, '1'), (3, 2, '2'),
(1, 3, '1'), (3, 1, '2'),
(3, 4, '1'), (4, 3, '2'),
(3, 5, '1'), (5, 3, '2');

SELECT u.*
FROM users_2 u
    LEFT JOIN banned_3 f ON u.id = f.user_1_id && f.user_2_id = 3
WHERE f.user_1_id IS NULL && u.id != 3
ORDER BY RAND()
LIMIT 1;

x-yuri, ну ты немного подогнал структуру базы, чтобы выполнить легко этот запрос(соединиться с одним полем второй таблицы). Дублирование информации все таки есть. Тут может оно и не критично, но например программировать операцию "Пользователь разбанивает всех своих забаненных" одним запросом не пройдет.

то твое сообщение(слив) появилось раньше чем "те мои слова" ....

Почитай подпись у B~Vladi
Человек не знает теорему Пифагора не потому что у него нет времени, а потому что он ее не знает ;)

Еще и минусанул смельчак. Правда глаза режет? Ребенок отомстил . :D . А на мои вопросы ответов так и нет у тебя. Это просто пипец. Мне тебя жаль. Идешь в игнор.

а теперь давайте сравним
создание таблиц:

-- users_2
drop table if exists users_2;
create table users_2 ( id int primary key );
insert into users_2 values (1), (2), (3), (4), (5), (6);

-- banned_2
drop table if exists banned_2;
create table banned_2 (
    idu int,
    idb int,
    primary key(idu, idb)
);
insert into banned_2 values
(2, 3),
(1, 3),
(3, 4),
(3, 5);

-- banned_3
drop table if exists banned_3;
create table banned_3 (
    user_1_id int,
    user_2_id int,
    origin enum('1', '2'),
    primary key(user_1_id, user_2_id)
);
insert into banned_3 values
(2, 3, '1'), (3, 2, '2'),
(1, 3, '1'), (3, 1, '2'),
(3, 4, '1'), (4, 3, '2'),
(3, 5, '1'), (5, 3, '2');

explain:

EXPLAIN
SELECT u.*
FROM users_2 u
    LEFT JOIN banned_3 f ON u.id = f.user_1_id && f.user_2_id = 3
WHERE f.user_1_id IS NULL && u.id != 3
ORDER BY RAND()
LIMIT 1;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: u
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 6
        Extra: Using index; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: f
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: u.id,const
         rows: 1
        Extra: Using where; Using index; Not exists

EXPLAIN
SELECT u.id
FROM users_2 u
WHERE u.id != 3
AND u.id NOT IN
  (
    SELECT IF(b.idu = 3, b.idb, b.idu)
    FROM banned_2 b
    WHERE b.idu = 3 OR b.idb = 3
  )
ORDER BY RAND()
LIMIT 1;
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: u
         type: index
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 6
        Extra: Using where; Using index; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: b
         type: index
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: NULL
         rows: 3
        Extra: Using where; Using index

EXPLAIN
SELECT id 
FROM users_2 U 
WHERE 
  ISNULL((SELECT id 
    FROM banned_2 
    WHERE idu=3 && idb=U.id || idb=3 && idu=U.id
    ))
ORDER BY RAND()
LIMIT 1;
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: U
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 6
        Extra: Using where; Using index; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: banned_2
         type: index
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: NULL
         rows: 3
        Extra: Using where; Using index

EXPLAIN
SELECT u.id, b.idu , b.idb
FROM users_2 u
LEFT JOIN banned_2 b
ON ((b.idu = 3 OR b.idb = 3) AND (u.id = b.idu OR u.id = b.idb))
WHERE ( (b.idu IS NULL) AND (b.idb IS NULL))
ORDER BY RAND()
LIMIT 1;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: u
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 6
        Extra: Using index; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: b
         type: index
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: NULL
         rows: 3
        Extra: Using where; Using index; Not exists

запуск на более-менее реальных данных:

mysql> SELECT u.*
    -> FROM users_2 u
    ->     LEFT JOIN filters_2 f ON u.id = f.user_1_id && f.user_2_id = 5
    -> WHERE f.user_1_id IS NULL && u.id != 5
    -> ORDER BY RAND()
    -> LIMIT 1;
+------+
| id   |
+------+
| 6569 |
+------+
1 row in set (0.28 sec)

mysql> SELECT u.id
    -> FROM users u
    -> WHERE u.id != 5
    -> AND u.id NOT IN
    ->   (
    ->     SELECT IF(b.idu = 5, b.idb, b.idu)
    ->     FROM banned b
    ->     WHERE b.idu = 5 OR b.idb = 5
    ->   )
    -> ORDER BY RAND()
    -> LIMIT 1;
+-------+
| id    |
+-------+
| 25324 |
+-------+
1 row in set (1 min 56.50 sec)

mysql> SELECT id
    -> FROM users_2 U
    -> WHERE
    ->   ISNULL((SELECT id
    ->     FROM banned_2
    ->     WHERE idu=5 && idb=U.id || idb=5 && idu=U.id
    ->     ))
    -> ORDER BY RAND()
    -> LIMIT 1;
+------+
| id   |
+------+
| 5419 |
+------+
1 row in set (1 min 56.53 sec)

mysql> SELECT u.id, b.idu , b.idb
    -> FROM users_2 u
    -> LEFT JOIN banned b
    -> ON ((b.idu = 5 OR b.idb = 5) AND (u.id = b.idu OR u.id = b.idb))
    -> WHERE ( (b.idu IS NULL) AND (b.idb IS NULL))
    -> ORDER BY RAND()
    -> LIMIT 1;
+------+------+------+
| id   | idu  | idb  |
+------+------+------+
| 9609 | NULL | NULL |
+------+------+------+
1 row in set (2 min 1.47 sec)

интересно, кстати, micscr, что хоть твой подзапрос вроде никак не связан с внешним, но mysql его пометил как DEPENDENT SUBQUERY

http://javascript.ru/forum/90147-post28.html
и я не говорил, что нужно недопустить дублирования. Дублирование оно тоже разное бывает

по поводу разбанивания:

Код:

---

DELETE FROM banned_3
WHERE origin = '1' && user_1_id = %s ||
    origin = '2' && user_2_id = %s

---

кстати, негативные последствия моего решения меня интересуют, ага :)

x-yuri, а можешь выложить сюда дампы? Я тоже погоняю.

С разбаниванием - да, точно. Строки все таки помечены направлениями. Дубляж по объему в 2 раза будет, но из цифр - не сильно значительный наверное. При большом числе пользователей- так наверное и лучше всего сделать. Я про оптимальность собственно и не думал. Миллионы зареганных одновременно пользователей на сайте - больше мечта чем реальность :) .

удалено.

это бажок в мускуле версий до 5.6.
Фиксится так: Добавь плиз индекс в таблицу banned_2:
и скажешь результат.

почти догнал :)

mysql> SELECT u.id
    -> FROM users_2 u
    -> WHERE u.id != 5
    -> AND u.id NOT IN
    ->   (
    ->     SELECT IF(b.idu = 5, b.idb, b.idu)
    ->     FROM banned_2 b
    ->     WHERE b.idu = 5 OR b.idb = 5
    ->   )
    -> ORDER BY RAND()
    -> LIMIT 1;
+-------+
| id    |
+-------+
| 26202 |
+-------+
1 row in set (0.54 sec)

explain:

*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: u
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 13847
        Extra: Using where; Using index; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: b
         type: index_merge
possible_keys: PRIMARY,idb
          key: PRIMARY,idb
      key_len: 4,4
          ref: NULL
         rows: 3
        Extra: Using union(PRIMARY,idb); Using where

а у меня стоит в PRIMARY KEY добавить origin, иначе если два пользователя попытаются друг друга забанить, один из них рискует получить ошибку 1062 Duplicate entry, а то и целостность нарушится

а по поводу DEPENDENT SUBQUERY, люди даже целую хранимую процедуру написали

:lol:

старался :)

а по поводу целостности, вы как считаете, надо ли рассматривать всякие неизвестно-насколько-вероятные-ситуации? Например, между первым и вторым INSERT'ом mysql вырубило. И вообще, можно ли считать, что INSERT либо выполнился либо нет? Ведь, например, с помощью INSERT можно вставлять несколько записей. Ну и речь не только об INSERT, конечно же

p.s. да, Gozar, должен признать, что чтение английских ресурсов сказывается на мне, но я не уверен, что это плохо :)

Одним insert-ом несколько записей - команда поступит к mysql и выполнится, разрыва думаю не может получиться.
Если сильно критично - возможность транзакций надо поюзать.

нет, дело не в критичности. Дело в выборе: MyISAM vs InnoDB, READ UNCOMMITTED vs READ COMMITTED vs REPEATABLE READ vs SERIALIZABLE. Исключительно в контексте целостности БД. В каких единицах измеряется критичность? В долларах чтоле? :lol: И как ее посчитать, а тем более спроецировать на выбор движка, уровня изоляции? И ты знаешь, в каких именно случаях может нарушиться целостность, без думаю?

Ну вот у тебя если сбой запроса произойдет и одна запись останется без ее дубля - целостность нарушена.
Но например твой код для user1 ищет кандидата user2, находит не того(из-за ошибки). Но перед соединением ты для user2 все равно проверяешь возможность работы с user1. Программа выяснит разногласие, не позволит соединиться и возможно устранит неточность. Или по крону. Т.е. приложение будет работать как надо.
Можно считать что возможность ошибки при записи - не критична :) .