gistfile1.sql

SELECT `sales`.* FROM `sales`
  INNER JOIN `properties` ON `properties`.`id` = `sales`.`property_id`
  WHERE (properties.zip = 33160)
  ORDER BY sale_date DESC
  LIMIT 10

Duration	56,163 ms

Explain

Id	 1
Select Type	 SIMPLE
Table	 properties
Type	 ref
Possible Keys	 PRIMARY,ix_zip
Key	 ix_zip
Key Length	 4
Ref	 const
Rows	 17920
Extra	 Using index; Using temporary; Using filesort

Id	 1
Select Type	 SIMPLE
Table	 sales
Type	 ref
Possible Keys	 ix_sales_home_id,ix_sales_property_date
Key	 ix_sales_home_id
Key Length	 9
Ref	 prod_southflorida.properties.id
Rows	 1
Extra	 Using where


My question is: what could I do to avoid this "Using index; Using temporary; Using filesort"?



CREATE TABLE `properties` (
  `id` bigint(20) NOT NULL DEFAULT '0',
  `county_id` int(11) DEFAULT '0',
  `parcel_id` varchar(40) NOT NULL DEFAULT '',
  `parcel_prefix` varchar(12) NOT NULL DEFAULT '',
  `num` int(11) DEFAULT '0',
  `num_max` int(11) NOT NULL DEFAULT '0',
  `num_alt` varchar(10) NOT NULL DEFAULT '',
  `dir` varchar(2) NOT NULL DEFAULT '',
  `street_type` varchar(10) DEFAULT '',
  `post_dir` varchar(2) DEFAULT '',
  `unit` varchar(10) NOT NULL DEFAULT '',
  `street_id` bigint(20) NOT NULL DEFAULT '0',
  `complex_id` bigint(20) NOT NULL DEFAULT '0',
  `tract_id` bigint(20) NOT NULL DEFAULT '0',
  `subdivision_id` bigint(20) NOT NULL DEFAULT '0',
  `neighborhood_id` bigint(20) NOT NULL DEFAULT '0',
  `township_id` bigint(20) NOT NULL DEFAULT '0',
  `association_id` bigint(20) NOT NULL DEFAULT '0',
  `community_id` bigint(20) NOT NULL DEFAULT '0',
  `city_id` bigint(20) NOT NULL DEFAULT '0',
  `zone_id` bigint(20) NOT NULL DEFAULT '0',
  `superzone_id` bigint(20) NOT NULL DEFAULT '0',
  `school_id` bigint(20) NOT NULL DEFAULT '0',
  `school2_id` bigint(20) NOT NULL DEFAULT '0',
  `school3_id` bigint(20) NOT NULL DEFAULT '0',
  `zip` int(11) NOT NULL DEFAULT '0',
  `use_code` varchar(20) DEFAULT '',
  `has_pool` tinyint(1) NOT NULL DEFAULT '0',
  `owner` varchar(100) NOT NULL DEFAULT '',
  `beds` decimal(3,1) NOT NULL DEFAULT '0.0',
  `baths` decimal(3,1) NOT NULL DEFAULT '0.0',
  `stories` tinyint(4) NOT NULL DEFAULT '0',
  `living_sf` int(11) NOT NULL DEFAULT '0',
  `land_sf` int(11) NOT NULL DEFAULT '0',
  `lat` double NOT NULL DEFAULT '0',
  `lon` double NOT NULL DEFAULT '0',
  `county` varchar(20) NOT NULL DEFAULT '',
  `year_built` int(4) NOT NULL DEFAULT '0',
  `user_id` int(11) NOT NULL DEFAULT '0',
  `property_tax` decimal(11,2) NOT NULL DEFAULT '0.00',
  `property_tax_cityrank` int(11) NOT NULL DEFAULT '0',
  `last_modified` datetime NOT NULL,
  `aprland` decimal(11,2) NOT NULL DEFAULT '0.00',
  `aprbldg` decimal(11,2) NOT NULL DEFAULT '0.00',
  `political_id` bigint(20) NOT NULL DEFAULT '0',
  `political2_id` bigint(20) NOT NULL DEFAULT '0',
  `political3_id` bigint(20) NOT NULL DEFAULT '0',
  `political4_id` bigint(20) NOT NULL DEFAULT '0',
  `floor_plan` varchar(100) NOT NULL DEFAULT '',
  `owner_is_unlisted` tinyint(1) NOT NULL DEFAULT '0',
  `region_id` bigint(20) DEFAULT '0',
  `as_of_date` date DEFAULT '0000-00-00',
  `address_slug` varchar(255) NOT NULL DEFAULT '',
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `builder_id` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `ix_folio` (`parcel_id`),
  KEY `ix_zip` (`zip`),
  KEY `ix_city` (`city_id`),
  KEY `ix_properties_complex` (`complex_id`),
  KEY `ix_properties_nhood` (`neighborhood_id`),
  KEY `ix_properties_streets` (`city_id`,`street_type`),
  KEY `ix_parcel_prefix` (`parcel_prefix`),
  KEY `use_code` (`use_code`),
  KEY `ix_school` (`school_id`),
  KEY `ix_school2` (`school2_id`),
  KEY `ix_school3` (`school3_id`),
  KEY `ix_political` (`political_id`),
  KEY `ix_political2` (`political2_id`),
  KEY `ix_political3` (`political3_id`),
  KEY `idx_subd` (`subdivision_id`),
  KEY `zone_idx` (`zone_id`),
  KEY `superzone_idx` (`superzone_id`),
  KEY `street_idx` (`street_id`),
  KEY `region_idx` (`region_id`),
  KEY `ix_tract` (`tract_id`),
  KEY `geo_idx` (`lat`,`lon`),
  KEY `market_idx` (`county_id`),
  KEY `ix_num` (`num`),
  KEY `properties_user_id` (`user_id`),
  KEY `properties_property_tax` (`property_tax`),
  KEY `builder_id` (`builder_id`),
  KEY `latlon` (`lat`,`lon`),
  KEY `township_id` (`township_id`),
  KEY `index_properties_on_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1


CREATE TABLE `sales` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `county_id` int(11) DEFAULT '0',
  `instrument` varchar(20) NOT NULL DEFAULT '',
  `property_id` bigint(20) DEFAULT NULL,
  `parcel_id` varchar(20) DEFAULT NULL,
  `buyer` varchar(500) NOT NULL DEFAULT '',
  `seller` varchar(500) NOT NULL DEFAULT '',
  `sale_date` date NOT NULL DEFAULT '0000-00-00',
  `sale_price` decimal(16,2) NOT NULL DEFAULT '0.00',
  `county` varchar(1) DEFAULT NULL,
  `buyer_id` int(11) DEFAULT '0',
  `buyer_role` varchar(1) NOT NULL DEFAULT '',
  `seller_id` int(11) DEFAULT '0',
  `seller_role` varchar(1) NOT NULL DEFAULT '',
  `is_new` tinyint(1) NOT NULL DEFAULT '0',
  `ownership` tinyint(4) NOT NULL DEFAULT '0',
  `trans_type` enum('','FC','FS','FB','FI','BB','BT','BO','LB','FA','FR') NOT NULL DEFAULT '',
  `last_modified` datetime DEFAULT '0000-00-00 00:00:00',
  `seller_agent_id` int(11) DEFAULT '0',
  `featured_seller` tinyint(1) NOT NULL DEFAULT '0',
  `buyer_agent_id` int(11) DEFAULT '0',
  `featured_buyer` tinyint(1) NOT NULL DEFAULT '0',
  `seller_is_unlisted` tinyint(1) NOT NULL DEFAULT '0',
  `buyer_is_unlisted` tinyint(1) NOT NULL DEFAULT '0',
  `zeroprice` int(11) DEFAULT '0',
  `city_id` bigint(20) DEFAULT '0',
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_id` (`instrument`),
  KEY `ix_sales_home_id` (`property_id`),
  KEY `ix_folio` (`parcel_id`),
  KEY `ix_sales_price` (`sale_price`),
  KEY `ix_sales_property_date` (`property_id`,`sale_date`),
  KEY `ix_sales_co` (`county`),
  KEY `ix_sales_type` (`trans_type`),
  KEY `ix_sales_seller_id` (`seller_id`),
  KEY `ix_last_modified` (`last_modified`),
  KEY `ix_sale_date` (`sale_date`),
  KEY `zeroprice` (`zeroprice`),
  KEY `ix_buyer_agent` (`buyer_agent_id`),
  KEY `ix_seller_agent` (`seller_agent_id`),
  KEY `market_idx` (`county_id`),
  KEY `sales_city_id` (`city_id`),
  KEY `zp_date` (`zeroprice`,`sale_date`),
  KEY `date_price` (`sale_date`,`sale_price`),
  KEY `index_sales_on_city_id_and_sale_date` (`city_id`,`sale_date`),
  KEY `index_sales_on_city_id_and_zeroprice` (`city_id`,`zeroprice`)
) ENGINE=InnoDB AUTO_INCREMENT=6744161 DEFAULT CHARSET=latin1