
CREATE TABLE `om_listings_search` (
  `omlid` int(11) NOT NULL,
  `zboardid` tinyint(4) NOT NULL,
  `latlng_point` point NOT NULL,
  `saletypeid` tinyint(4) NOT NULL,
  `bedrooms` tinyint(4) NOT NULL,
  `bathrooms` tinyint(4) NOT NULL,
  `lp_typeid` tinyint(4) NOT NULL,
  `min_sqft` smallint(6) NOT NULL,
  `max_sqft` smallint(6) NOT NULL,
  `list_price` int(11) NOT NULL,
  `sold_date_int` int(11) NOT NULL,
  `statusid` tinyint(4) NOT NULL,
  PRIMARY KEY (`omlid`),
  SPATIAL KEY `latlng` (`latlng_point`)
) ENGINE=InnoDB;

insert into 
  om_listings_search 
select
  seq, 
  123,
  GeomFromText(concat('POINT(', MOD(seq, 100), ' ', MOD(seq, 90), ')')),
  1, 2, 3, 4, 5, 6, 100500,1,2
from seq_1_to_10000;


explain extended  
select omlid 
from om_listings_search force index (latlng) 
where  
  saletypeid='1'   and 
  bathrooms between 2-1 and 2+1 and 
  min_sqft>=1200*0.9 and max_sqft<=1200*1.2   and 
  bedrooms >=4   and lp_typeid=3  and  
  MBRContains(
    GeomFromText('POLYGON((49.004442954955 -122.34298052941, 49.094533045045 -122.34298052941, 49.094533045045 -122.22533347059, 49.004442954955 -122.22533347059,  49.004442954955 -122.34298052941 ))'), 
    latlng_point
  ) and 
  statusid=1   
order by 
  sold_date_int desc 
limit 6;


