title: MySQL Left join take too long for huge data
body: <p>I have two tables .Property tables and it related photo.One property may have many photo but I want only one any of it related photo, When I use left join MySQL query it become too slow.</p>
  
  <p>Here is my query</p>
  
  <pre><code>SELECT `sp_property`.`id` as propertyid, `sp_property`.`property_name`, `sp_property`.`property_price`, `sp_property`.`adv_type`, `sp_property`.`usd`, `images`.`filepath_name` 
  FROM (`sp_property`) 
  LEFT JOIN  (select id, Max(property_id) as pid,filepath_name 
              from sp_property_images
              group by property_id) `images`
  ON `images`.`pid` = `sp_property`.`id` 
  WHERE `sp_property`.`published` = 'yes' 
  GROUP BY `propertyid` 
  ORDER BY `sp_property`.`feature_listing` desc, `submit_date` desc
  LIMIT 1,20
  
  
  
  CREATE TABLE IF NOT EXISTS `sp_property_images` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `property_id` varchar(100) NOT NULL,
    `filepath_name` text,
    `label_name` varchar(45) DEFAULT NULL,
    `primary` char(10) DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `property_id` (`property_id`),
    KEY `id` (`id`)
  ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=12941 ;
  
  CREATE TABLE IF NOT EXISTS `sp_property` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `propertytype` varchar(50) NOT NULL,
    `adv_type` varchar(45) NOT NULL,
    `property_name` text,
    `division` varchar(45) NOT NULL,
    `township` varchar(45) NOT NULL,
    `property_price` decimal(20,2) unsigned DEFAULT NULL,
    `price_type` varchar(45) NOT NULL,
    `availability` varchar(100) DEFAULT NULL,
    `property_address` text,
    `p_dimension_length` varchar(45) NOT NULL,
    `p_dimension_width` varchar(45) NOT NULL,
    `p_dimension_sqft` varchar(45) NOT NULL,
    `p_dimension_acre` varchar(45) NOT NULL,
    `floor` varchar(45) NOT NULL,
    `phone` varchar(100) DEFAULT NULL,
    `aircorn` varchar(45) NOT NULL,
    `ownership` varchar(45) NOT NULL,
    `bedroom` varchar(45) NOT NULL,
    `bathroom` varchar(45) NOT NULL,
    `special_feature` text,
    `amentites` text,
    `property_detail` text,
    `submit_date` datetime DEFAULT NULL,
    `published` varchar(45) NOT NULL,
    `published_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `agent_id` varchar(45) NOT NULL,
    `source` varchar(45) NOT NULL,
    `contact_name` varchar(100) NOT NULL,
    `contact_no` varchar(100) NOT NULL,
    `contact_address` text NOT NULL,
    `contact_email` varchar(100) NOT NULL,
    `unit_type` varchar(100) DEFAULT NULL,
    `map_lat` varchar(100) DEFAULT NULL,
    `map_long` varchar(100) DEFAULT NULL,
    `show_map` varchar(3) DEFAULT 'no',
    `total_view` bigint(20) NOT NULL DEFAULT '0',
    `feature_listing` varchar(10) NOT NULL DEFAULT 'no',
    `new_homes_id` int(11) NOT NULL,
    `publish_price` int(1) NOT NULL DEFAULT '0',
    `usd` decimal(20,2) NOT NULL,
    `tag_id` int(11) NOT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=18524 ;
  </code></pre>