Как выполнить такой тип запроса в MySQL?

Question

[vladimir_vist]

Есть таблица "table"
| id | user_id | a_id | b_id |

SELECT * FROM `table` WHERE `user_id` = 1 

| id | user_id | a_id | b_id |
  1    1         2         4
  5    1         7         5
  7    1         3         4
  8    1         9         5

Нужно имея входные данные user_id = 1, выбрать столбцы привязанные к другим пользователям, но совпадающие по a_id && b_id

В идеале еще и посчитать кол-во совпадений, и получить такой результат

| user_id | matches_row_count |
  5         1
  8         5
  9         32
  10        7

На Python я бы описал это так:

user_id = 1
user_rows = []

for row in table:
	if row.user_id == user_id:
		user_rows.append(row)
		
result = {}
for user_row in user_rows:
	for row in table:
		if (user_row.a_id == row.a_id) and (user_row.b_id == row.b_id) and (user_row.id != row.user_id):
			if row.user_id not in result:
				result[row.user_id] = 1
			else:
				result[row.user_id]++

И как можно оптимизировать такие запросы? Возможно перестроить таблицу, или сделать кастомный индекс?

Answer 1

[Slava Rozhnev]

select 
	row.user_id, count(*) matches_row_count
from users user_row
join users row on 
	(user_row.a_id = row.a_id) and 
	(user_row.b_id = row.b_id) and 
	(user_row.id != row.user_id)
where user_row.user_id = 1
group by row.user_id;

Test query online

Answer 2

[Rsa97]

JOIN, GROUP BY, COUNT()

Comments

[vladimir_vist]

А как в этом запросе для JOIN указать условие именно a_id && b_id а не просто a_id || b_id?

[vladimir_vist]

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

[Константин Б.]

vladimir_vist, куда быстрее чем на питоне

[Rsa97]

vladimir_vist, Так и указать Условие AND Условие
Скорость запроса будет зависеть от того какие индексы есть по таблице.