How to perform this type of query in MySQL?

Question

MySQL

There is a 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

It is necessary, having user_id = 1 input data, to select columns associated with other users, but matching by a_id && b_id

Ideally, also count the number of matches, and get such a result

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

In Python, I would describe it like this:

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]++

And how can such queries be optimized? Is it possible to rebuild the table, or make a custom index?

Answer 1

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

JOIN, GROUP BY, COUNT()