title: mySQL query is very slow after using DISTINCT and GROUP BY?
body: <p>I have tables with following structure: </p>
  
  <pre><code>-- Table structure for table `temp_app`
  --
  
  CREATE TABLE IF NOT EXISTS `temp_app` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `vid` int(5) NOT NULL,
    `num` varchar(64) NOT NULL,
    `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `vid` (`vid`),
    KEY `num` (`num`),
  ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=69509;
  
  -- Table structure for table `inv_flags`
  --
  
  CREATE TABLE IF NOT EXISTS `inv_flags` (
    `num` varchar(64) NOT NULL,
    `vid` int(11) NOT NULL,
    `f_special` tinyint(1) NOT NULL, /*0 or 1*/
    `f_inserted` tinyint(1) NOT NULL, /*0 or 1*/
    `f_notinserted` tinyint(1) NOT NULL, /*0 or 1*/
    `userID` int(11) NOT NULL,
    `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY `num` (`num`),
    KEY `userID` (`userID`),
    KEY `vid` (`vid`),
    KEY `timestamp` (`timestamp`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  </code></pre>
  
  <p>Execution time of the following query is 9 seconds to display 30 records. What is wrong?</p>
  
  <pre><code>SELECT date_format(ifs.`timestamp`,'%y/%m/%d') as `date`
                  ,count(DISTINCT ta.num) as inserted /*Unique nums*/
                  ,SUM(ifs.f_notinserted) as not_inserted
                  ,SUM(ifs.f_special)  as special
                  ,count(ta.num) as links /*All nums*/
                  from inventory_flags ifs
                  LEFT JOIN temp_app ta ON ta.num = ifs.num AND ta.vid = ifs.vid
                  WHERE ifs.userID = 3
                  GROUP BY date(ifs.`timestamp`) DESC LIMIT 30
  </code></pre>
  
  <p><strong>EXPLAIN RESULT</strong></p>
  
  <pre><code>id  select_type table   type    possible_keys   key key_len ref rows    Extra
  1   SIMPLE  ifs ref userID  userID  4   const   12153   Using where
  1   SIMPLE  ta  ref vid,num num 194 ifs.num 1   
  </code></pre>