0

I have an issue with a search function, it works correctly but is taking between 14-18 second to load and that is causing me great pain.

This is the search function I am using:

$sql="select *, 
( 3959 * acos( cos( radians( $lat ) ) * cos( radians( `lat` ) ) * cos( radians( `lng` ) - radians( $lng ) ) + sin( radians( $lat ) ) * sin( radians( `lat` ) ) ) )  AS distance 
  FROM postcodes,
       details 
 WHERE `postcode`=postcodes.outcode 
 HAVING " . implode(" AND ", $searches)." 
    AND isactive='y' 
 ORDER BY `distance`";

If anyone has any suggestions they would all be welcomed

I have spent more time working on it, and understand exactly what the query is doing before I added any more information.

This is now my updated query, I tried using a left join but I couldn't join the postcode table onto the cardetails table, and when I reversed it the cardetails columns where all showing up null.

SELECT "column names",  
( 3959 * acos( cos( radians( $lat ) ) * cos( radians( `lat` ) ) * cos( radians(
`lng`   ) - radians( $lng ) ) + sin( radians( $lat ) ) * sin( radians( `lat` ) ) )) 
AS       distance
FROM details
inner JOIN car_postcodes ON details.postcode =  postcodes.outcode AND
distance  < 100 AND payment='y' AND expired='n' ORDER BY     
details.price

That has drastically reduced the time taken to perform the query (down to around 6seconds) so I still need to improve that.

The problem I am having now is that I am not able to use the column alias 'distance' to measure the distance between the user and the field within the table. As I cant use column alias to within the where clause.

The answer I seem to have read on many other posts is a sub select,

Matt M
  • 43
  • 6
  • No join between the postcodes and details tables? So for every postcode, it's going to be joining against every single details record – Mark Baker Dec 24 '13 at 12:06
  • How about limiting the number of results? That should cut down the time. – Akshat Singhal Dec 24 '13 at 12:06
  • And why use `HAVING` rather than simply using the `WHERE` clause for your searches? Are you aware of what HAVING is used for? You're not even using any aggregated data values. MySQL will fetch all rows that match a WHERE clause, using indexing as appropriate; and only then is the HAVING applied – Mark Baker Dec 24 '13 at 12:09
  • Have you tried the explain function of mysql (assuming it is mysql database) to tell what is taking so long? The calculation you are running is fairly complex. I think it might be better to first create a subset, and then do the calculation. – Jelle Ferwerda Dec 24 '13 at 12:10

1 Answers1

0

I'm not sure what you are doing but the following line is suspicious

FROM postcodes,details

If I'm not mistaken, this is a crossjoin (if your tables are large this will just multiply the dimensions and end up with..., I'm just not sure what you're trying to do here)

hanzo2001
  • 1,338
  • 1
  • 10
  • 24