title: Explain select output on a complex query that I didn't write
body: <p>I'm working on a codebase I didn't write and we're getting very bad performance on our SQL queries, so I thought we're probably missing some indices, having badly-written queries, etc. We are using MySQL. I can already see the way we query by name of genres instead of some kind of genre id that the DB is a mess. I am trying to do an explain query to figure out why this query is the biggest offender in terms of run time.</p>
  
  <pre><code>EXPLAIN 
   SELECT COUNT(v.id) cnt
     FROM non_detail_video_views vv
     JOIN video v
       ON vv.video_id = v.id
     JOIN video_sub_category c
       ON v.video_sub_category_id = c.id
    WHERE DATE(FROM_UNIXTIME(vv.created_date)) = '2014-06-25'
      AND c.sub_category_name IN ('Rap','R&amp;B','Country','Pop','Metal','Rock'
                                 ,'EDM','Christian','Alternative','Jazz','Blues'
                                 ,'Reggae','Classical','Folk','Trance') 
    GROUP 
       BY vv.video_id
    ORDER 
       BY vv.created_date DESC;
  </code></pre>
  
  <p>I asked the guy with the direct access to the DB to run the query and he gave me a screenshot back:</p>
  
  <p><img src="https://i.stack.imgur.com/PSRuz.png" alt="query"></p>
  
  <p>This is the non_detail_video_views table:</p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS `non_detail_video_views` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `video_id` int(11) NOT NULL,
    `sharer_id` int(11) NOT NULL,
    `member_id` int(11) NOT NULL,
    `is_newgame` int(11) NOT NULL,
    `frompage` int(11) NOT NULL,
    `from_country` varchar(255) NOT NULL,
    `ip_address` varchar(255) NOT NULL,
    `created_date` int(11) NOT NULL,
    `updated_date` int(11) NOT NULL,
    `active` int(11) NOT NULL,
    PRIMARY KEY (`id`),
    KEY `id` (`id`),
    KEY `video_id` (`video_id`),
    KEY `created_date` (`created_date`)
  ) ENGINE=InnoDB  DEFAULT CHARSET=latin1;
  </code></pre>
  
  <p>Video table:</p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS `video` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `referral_id` int(11) NOT NULL,
    `user_id` int(11) NOT NULL,
    `video_title` varchar(255) NOT NULL,
    `video_description` varchar(2048) DEFAULT NULL,
    `video_url` varchar(1024) NOT NULL,
    `video_html` varchar(2048) NOT NULL,
    `video_category_id` int(11) NOT NULL,
    `video_sub_category_id` int(11) NOT NULL,
    `video_thump` varchar(255) NOT NULL,
    `price` decimal(10,2) NOT NULL,
    `payment_type` int(11) NOT NULL,
    `in_contest` int(11) NOT NULL DEFAULT '0',
    `repeater` int(11) NOT NULL DEFAULT '1',
    `coupon_code` varchar(255) NOT NULL,
    `created_date` int(11) NOT NULL,
    `updated_date` int(11) NOT NULL,
    `active` int(11) NOT NULL,
    `noofshares` varchar(255) NOT NULL,
    `besides` int(11) NOT NULL,
    `facebookpage` varchar(255) NOT NULL,
    `custom_message` varchar(255) NOT NULL,
    `custom_link` varchar(255) NOT NULL,
    `video_country_id` varchar(255) NOT NULL,
    `ages` varchar(255) NOT NULL,
    `gender` int(11) NOT NULL,
    `duration` int(11) NOT NULL,
    `likes` int(11) NOT NULL,
    `views` int(11) NOT NULL,
    `comments` varchar(255) NOT NULL,
    `subscribers` varchar(255) NOT NULL,
    `pay_per_view` double(10,2) NOT NULL,
    `total_payment` double(10,2) NOT NULL,
    `pending_amount` double(10,2) NOT NULL,
    `second_payment` int(11) NOT NULL,
    `welcome_mail` int(11) NOT NULL,
    `add_budget_mail` int(11) NOT NULL,
    `auto_mid_way` int(11) NOT NULL,
    `out_of_budget` int(11) NOT NULL,
    `out_of_budget_2` int(11) NOT NULL,
    `out_of_budget_3` int(11) NOT NULL,
    `is_refunded` int(11) NOT NULL,
    `refund_date` int(11) NOT NULL,
    `is_banned` int(11) NOT NULL,
    PRIMARY KEY (`id`),
    KEY `id` (`id`),
    KEY `video_id` (`video_url`(767)),
    KEY `created_date` (`created_date`)
  ) ENGINE=InnoDB  DEFAULT CHARSET=latin1;
  </code></pre>
  
  <p>video_sub_category table:</p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS `video_sub_category` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `category_id` int(11) NOT NULL,
    `sub_category_name` varchar(255) NOT NULL,
    `genre_id` int(11) NOT NULL,
    `created_date` int(11) NOT NULL,
    `updated_date` int(11) NOT NULL,
    `active` int(11) NOT NULL DEFAULT '1',
    PRIMARY KEY (`id`)
  ) ENGINE=InnoDB  DEFAULT CHARSET=latin1;
  </code></pre>
  
  <p>I haven't done this before, how do I interpret the output?</p>