Way to speed up this "lat/lng" mySQL query a bit?

[youradds]

Hi guys,

I'm trying to clean up this query a bit, to make it quicker:

SELECT 
			ID, Ville, Title, Image1, Telephone, ExpiryDate,
			Address, Postal_Code, Latitude, Longitude, 
			
			( 6371 * acos( cos( radians('42.93481749134106') ) * cos( radians( Latitude ) ) * cos( radians( Longitude ) - radians('2.222607135772705') ) + sin( radians('42.93481749134106') ) * sin( radians( Latitude ) ) ) ) AS distance
			
			FROM glinks_Links
				HAVING 
					
					ExpiryDate < 2147483647 
					AND ExpiryDate > 131486702		
                                       AND distance < '25' 
			ORDER BY distance LIMIT 10

Its currently taking 0.29 seconds, which seems a long time :/ There are only 12,500 odd rows in the glinks_Links table, so I'm a little confused as to why its taking so long?

TIA!

Andy

 

[Olavxxx]

Hi, do you have indexes? I also use location-diff for querying a large resultset. Mine is lightning fast and I use 3 tables with an outer join.

Sorry I cant share the db design, as I made it for a client.


Olav Alexander Mjelde

 

[Olavxxx]

Btw. I use InnoDB as storage engine, that might also affect performance

Olav Alexander Mjelde

 

[youradds]

Hi,

Thanks for the reply. The only reason I can see it being so slow, is due to the fact there are a LOT of fields for each record.

I'm currently using myISAM. Doing an "EXPLAIN" on the query, it doesn't seem to be finding any indexes:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE glinks_Links ALL NULL NULL NULL NULL 12660 Using filesort

The problem is, I'm not really sure which fields I should add the indexes to? I'm guessing it would be:

Latitude
Longitude
ExpiryDate

I'm aware the order of these fields are important too, so any suggestions would be good (as my index with those values doesn't seem to get picked up)

TIA

Andy

 

[youradds]

Ok, to try and speed up the selection process, I've made a new table, which just had 4 fields:

ID
Latitude
Longitude
ExpiryDate

Then I'm using this query to get the results:

SELECT
             *, 
			( 6371 * acos( cos( radians('47.9646692234472') ) * cos( radians( Latitude ) ) * cos( radians( Longitude ) - radians('7.235441207885742') ) + sin( radians('47.9646692234472') ) * sin( radians( Latitude ) ) ) ) AS distance
            FROM glinks_Links_Maps
                HAVING
                    ExpiryDate < 2147483647
                    AND ExpiryDate > 1317207954
                    AND distance < '25'
            ORDER BY distance LIMIT 10

This works fine (takes 0.00007 seconds to run), but then what I have to do for each result, is grab the record from the Links table as well:

SELECT * FROM glinks_Links WHERE ID = xxxx

I'm trying to achieve the same thing, but using a JOIN - and it keeps crashing the server. Can someone point me in the right direction? I don't normally use JOIN in my SQL, as I've not really have the need to - so it could be something crappy with my syntax

SELECT
            glinks_Links.*, glinks_Links_Maps.*, 
			( 6371 * acos( cos( radians('47.9646692234472') ) * cos( radians( glinks_Links_Maps.Latitude ) ) * cos( radians( glinks_Links_Maps.Longitude ) - radians('7.235441207885742') ) + sin( radians('47.9646692234472') ) * sin( radians( glinks_Links_Maps.Latitude ) ) ) ) AS distance
            FROM glinks_Links_Maps, glinks_Links
                HAVING
                    glinks_Links_Maps.ExpiryDate < 2147483647
                    AND glinks_Links_Maps.ExpiryDate > 1317207954
                    AND distance < '25'
		AND (glinks_Links_Maps.ID = glinks_Links.ID)					
            ORDER BY distance LIMIT 10

TIA!

Andy

 

[r937]

SELECT glinks_Links.*
     , glinks_Links_Maps.*
     , ( 6371 * acos( cos( radians('47.9646692234472') ) * 
                      cos( radians( glinks_Links_Maps.Latitude ) ) * 
                      cos( radians( glinks_Links_Maps.Longitude ) - 
                           radians('7.235441207885742') ) + 
                      sin( radians('47.9646692234472') ) * 
                      sin( radians( glinks_Links_Maps.Latitude ) ) ) ) 
         AS distance          
  FROM [blue]glinks_Links_Maps
INNER 
  JOIN glinks_Links
    ON glinks_Links.ID = glinks_Links_Maps.ID [/blue]
 WHERE [red]glinks_Links_Maps.ExpiryDate > 1317207954[/red]
HAVING distance < 25
ORDER 
    BY distance LIMIT 10

the part in blue is how you should write the join, not in the HAVING clause

the part in red is moved from the HAVING clause to the WHERE clause (note i also dropped the "less than 2147483647" because it's redundant)

finally (although this has nothing to do with performance), you should not compare a numeric value, like distance, to a string, like '25' -- compare it to a number, like 25, instead

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[youradds]

Hi,

Thanks, that did the trick

(note i also dropped the "less than

" because it's redundant)

Actually, its not really In our system, when a listing has an expiry date of

, its a "Free" listing. When the ExpiryDate is less than that, its a paid listing (and the converted timestamp is the actual expire time). Either way, it works like a charm now - thanks!

Andy