title: How to optimize group by in table with huge number of records
body: <p>I have a Person table with huge number of records(for about 16 million), and have a requirement to find all persons, with same <code>lastname</code>, first letter of <code>firstname</code> and <code>birthyear</code>, in other worlds I want to show assuming duplicate persons in UI for users to analyze and decide are there a same person or not.</p>
  
  <p>Here is the query I write</p>
  
  <pre><code>SELECT * 
  FROM Person INNER JOIN
  (
      SELECT SUBSTRING(firstName, 1, 1) firstNameF,lastName,YEAR(birthDate) birthYear
      FROM Person
      GROUP BY SUBSTRING(firstName, 1,1),lastName,YEAR(birthDate)
      HAVING count(*) &gt; 1
  ) as dupPersons 
  ON SUBSTRING(Person.firstName,1,1) = dupPersons.firstNameF and Person.lastName = dupPersons.lastName and YEAR(Person.birthDate) = dupPersons.birthYear
  order by Person.lastName,Person.firstName
  </code></pre>
  
  <p>but as I am not SQL expert, want too know, is this good way to do that? are there more optimized way?</p>
  
  <p><strong>EDIT</strong></p>
  
  <p>Note that I can cut data, which can have contribution in optimization</p>
  
  <p>for example if I want to cut data by 2 it could return two persons</p>
  
  <pre><code>Johan Smith |
  Jane Smith  | have same lastname and first name inita 
  Jack Smith  |
  
  Mark Tween  | have same lastname and first name inita 
  Mac Tween   |
  </code></pre>