Sql: Counting And Numbering Duplicates - Optimising Correlated Subquery

In an SQLite database I have one table where I need to count the duplicates across certain columns (i.e. rows where 3 particular columns are the same) and then also number each of

Solution 1:

A self join may be faster than a correlated subquery

SELECT d1.id, d1.match1, d1.match2, d1.match3, d1.data, count(*) matchid
FROM idcountdata d1
JOIN idcountdata d2 on d1.match1 = d2.match1 
  and d1.match2 = d2.match2 
  and d1.match3 = d2.match3
  and d1.id >= d2.id
GROUPBY d1.id, d1.match1, d1.match2, d1.match3, d1.data

This query can take advantage of a composite index on (match1,match2,match3,id)