MYSQL select count two table left join I have two table. I want count how many times a value is in other table. So the codice from table &quot;sorgente&quot; is different in table contatore, because I have the suffix 'BRANO-' before the code.
sorgente
| codice | nome  |
|   15   | mario |
|   16   | mary  |

contatore
| nome_evento |   data     |
| BRANO-15    | 2020-08-15 |
| BRANO-15    | 2020-08-16 |
| BRANO-16    | 2020-08-14 |

the query for select is
select
    s.codice,
    (
        select count(*) 
        from contatore c 
        where c.nome_evento = concat('BRANO-', s.codice) 
    ) no_matches_in_contatore
from sorgente s

but the original &quot;sorgente&quot; table has 25000 records, the &quot;contatore&quot; table has at now 60000 records.
when I try to execute the query it takes 600 seconds....
can you help me?
You should try below query, also let me know what indexes are on your tables.
  WITH cte1 as (select SUBSTR(nome_evento, instr(nome_evento, '-') + 1) as nome_evento  from contatore)
SELECT count(b.codice),
        b.codice 
FROM cte1 a 
INNER JOIN 
sorgente b 
ON (a.nome_evento = b.codice) 
group by b.codice;