MYSQL select count two table left join
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;