[eluser]Lewis Cowles[/eluser]
Right, I have carried out some tests and posted times for the queries for a modified version of the broad phase test as well as a much higher performance version of the narrow-phase which is now the only check, tested for 300,000 dummy records
Broad Phase New
Code:
/*
* new plan for sql search to make faster is to search within dates and lattitude & longitude within xmin,xmax and ymin,ymax (rectangle point collision) precomputed by php
*/
// 0.0005 secs to query (1)
// 0.0007 secs to query (2)
// 0.0376 secs to query (3)
// 0.0166 secs to query (4)
// 0.0008 secs to query (5)
// 0.0008 secs to query (6)
// 0.0026 secs to query (7)
$query1 = sprintf("SELECT available_venues.id AS 'venue_id', available_venues.availability, available_venues.address_id, available_venues.date, address_details.id AS 'address_id', postcodes.id AS 'postcode_id', postcodes.postcode FROM available_venues, address_details, postcodes WHERE available_venues.date BETWEEN '%s' AND '%s' AND address_details.id = available_venues.address_id AND address_details.postcode_id = postcodes.id AND available_venues.availability > 0 AND postcodes.latitude BETWEEN %f and %f AND postcodes.longitude BETWEEN %f and %f",
mysql_real_escape_string($datefrom),
mysql_real_escape_string($dateto),
$postcodes->latitude + ($this->change_in_latitude($radius)*-1),
$postcodes->latitude + $this->change_in_latitude($radius),
$postcodes->longitude + ($this->change_in_longitude($postcodes->latitude,$radius)*-1),
$postcodes->longitude + $this->change_in_longitude($postcodes->latitude,$radius)
);
OLDER NARROW-PHASE CHECK, modified for faster searching (I remembered AS slows queries nominally and checks with AND drop out immediately upon false VALUES)
Code:
// 0.0278 secs to query
// 0.0008 secs to query (2)
// 0.0075 secs to query (3)
// 0.0008 secs to query (4)
// 0.0008 secs to query (5)
// 0.0025 secs to query (6)
// 0.0038 secs to query (7)
$query1 = sprintf("SELECT available_venues.id AS 'venue_id', available_venues.availability, available_venues.address_id, available_venues.date, address_details.id AS 'address_id', postcodes.id AS 'postcode_id', postcodes.postcode FROM available_venues, address_details, postcodes WHERE ( 3958.7558 * ACOS( COS( RADIANS( '%s' ) ) * COS( RADIANS( postcodes.latitude ) ) * COS( RADIANS( postcodes.longitude ) - RADIANS( '%s' ) ) + SIN( RADIANS( '%s' ) ) * SIN( RADIANS( postcodes.latitude ) ) ) ) < %f AND available_venues.date BETWEEN '%s' AND '%s' AND address_details.id = available_venues.address_id AND address_details.postcode_id = postcodes.id AND available_venues.availability > 0",
mysql_real_escape_string($postcodes->latitude),
mysql_real_escape_string($postcodes->longitude),
mysql_real_escape_string($postcodes->latitude),
mysql_real_escape_string($radius),
mysql_real_escape_string($datefrom),
mysql_real_escape_string($dateto)
);
In the future this will be implemented using a single select with active-record in CI, also as a note I think the performance differences are nominal, however the rectangular check is less accurate!
thanks for all your help guys!
