title: MySQL Count Distinct - Very Slow
body: <p>I have a very big MySQL InnoDB table with following structure:</p>
  
  <pre><code>TABLE `whois_records` (
    `record_id` int(10) unsigned NOT NULL,
    `domain_name` varchar(100) NOT NULL,
    `tld_id` smallint(5) unsigned DEFAULT NULL,
    `create_date` date DEFAULT NULL,
    `update_date` date DEFAULT NULL,
    `expiry_date` date DEFAULT NULL,
    `query_time` datetime NOT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  
  PRIMARY KEY (`record_id`)
  UNIQUE KEY `domain_time` (`domain_name`,`query_time`)
  INDEX `tld_id` (`tld_id`)
  </code></pre>
  
  <p>This table currently has 10 Million rows.
  It stores frequently updated details of domain names.
  So there can be multiple records for same domain name in the table.</p>
  
  <p>TLD ID is the numeric value of the type of domain extension.</p>
  
  <p>Problem is when I'm trying to count the total number of domain names of a particular TLD.</p>
  
  <p>I have tried the following 3 SQL queries:</p>
  
  <pre><code>SELECT COUNT(DISTINCT(domain_name)) FROM `whois_records` WHERE tld_id=159
  SELECT COUNT(*) FROM `whois_records` WHERE tld_id=159 GROUP BY domain_name
  SELECT COUNT(*) FROM ( SELECT 1 FROM `whois_records` WHERE tld_id=159 GROUP BY domain_name) q
  </code></pre>
  
  <p>All the 3 are very slow, taking between 5 to 10 minutes. It is also using up a lot of CPU to complete. There is INDEX defined on the TLD ID column, so these queries might be doing a FULL INDEX SCAN. It is still very slow. TLD ID of 159 is for ".com", which are the most in number. So when doing a search for 159, it is slowest. For non-popular TLD, with less than 100 domains, the same query takes around 0.10 seconds. TLD ID 159 has around 6 Million records, which is 60% of the entire table consisting of 10 Million rows.</p>
  
  <p>Is there any way to optimize the calculation?</p>
  
  <p>As table grows, the current queries will take longer. So please can anyone help me with a future proof solution to this problem. Is any alteration of table required? Plz help, thank you :)</p>