title: View with join between field that can be null
body: <p>I have 2 tables with more than 50,000 records each, each one of these.</p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS `call_attempt` (
    `csvleads_id_fk` int(11) NOT NULL,
    `users_id_fk` int(11) NOT NULL,
    `status` int(11) NOT NULL,
    `call_id` varchar(50) default NULL,
    KEY `csvlead` (`csvleads_id_fk`),
    KEY `user_id` (`users_id_fk`)
  ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
  
  CREATE TABLE IF NOT EXISTS `recorded` (
    `record_id` int(11) NOT NULL auto_increment,
    `agent_id_fk` int(11) NOT NULL,
    `lead_id_fk` int(11) NOT NULL,
    `duration` int(11) NOT NULL,
    `recording_url` text collate utf8_unicode_ci NOT NULL,
    `recorded_at` timestamp NULL default NULL,
    `call_id` varchar(50) character set utf8 default NULL,
    PRIMARY KEY  (`record_id`),
    UNIQUE KEY `filterme` (`recording_url`(100))
  ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=72158 ;
  </code></pre>
  
  <p>I added a call_id field to make the link. This means that new records must have these fields filled, at least in table <strong>call_attempt</strong>. </p>
  
  <pre><code>  call_attempt               recorded
  -------------------       ----------------
  users_id_fk call_id       call_id dutarion
  -------------------       ----------------
  1            NULL         NULL    10
  2            NULL         NULL     8
  3            NULL         NULL     5
  4            NULL         ca12    19
  50000        ca12         ca14     9
  50001        ca13
  50002        ca14         
  </code></pre>
  
  <p>I need to throw me a view that all records in the table <strong>call_attempt</strong>, and if you find records in table <strong>recorded</strong> the duration is brought.</p>
  
  <hr>
  
  <p>I tried LEFT JOIN but takes more than 50 seconds. </p>
  
  <pre><code>SELECT 
          `cal`.`csvleads_id_fk` AS `Id`,
          `rc`.`duration` AS `duration`
      FROM `call_attempt` AS cal       
          LEFT JOIN recorded AS rc ON (cal.call_id = rc.call_id AND (rc.call_id IS NOT NULL))
  </code></pre>
  
  <p><em>84.625 sec / 0.000 sec</em></p>
  
  <hr>
  
  <p>Next, I tried doing a LEFT JOIN to a SELECT  table <strong>recorded</strong> with only the records that <strong>call_id</strong> are NOT NULL but don't let me create the view. </p>
  
  <pre><code>CREATE 
      ALGORITHM = UNDEFINED 
      DEFINER = `benscott`@`%` 
      SQL SECURITY DEFINER
  VIEW `callHistory4` AS
  SELECT 
          `cal`.`csvleads_id_fk` AS `Id`,        
          `rc`.`duration` AS `duration`
      FROM `call_attempt` AS cal              
          LEFT JOIN (SELECT call_id , duration FROM `recorded` WHERE call_id IS NOT NULL) AS rc ON (rc.call_id = cal.call_id)
  </code></pre>
  
  <p><em>Error Code: 1349. View's SELECT contains a subquery in the FROM clause</em></p>
  
  <hr>
  
  <p>Then I create another view with just the results NOT NULL from <strong>call_id</strong>. </p>
  
  <pre><code>CREATE 
      ALGORITHM = UNDEFINED 
      DEFINER = `benscott`@`%` 
      SQL SECURITY DEFINER
  VIEW `view_recorded` AS
  SELECT call_id , duration FROM `recorded` WHERE call_id IS NOT NULL
  </code></pre>
  
  <p>And in the new view I join with view_recorded but also takes more than 40 seconds.</p>
  
  <pre><code>SELECT 
          `cal`.`csvleads_id_fk` AS `Id`,
          `rc`.`duration` AS `duration`
      FROM `call_attempt` AS cal       
          LEFT JOIN view_recorded AS rc ON (rc.call_id = cal.call_id)
  </code></pre>
  
  <p><em>92.094 sec / 0.000 sec</em></p>
  
  <hr>
  
  <p>So, what options Do I have? Thank you!!</p>