3

I'm building a website with PHP and MYSQL.

I'm trying to order my search results by nearest location first. I have a items table and a deals table. I need to search BOTH of these tables for the item/deal location and then compare the location to my postcode database and get the latitude and longitude from the corresponding database entry and sort the results. I'm using PDO prepared statements.

I have 3 tables in my Database. Postcodes, Deals and Items.

POSTCODES

| postcode | lat | lng |

DEALS

| id | title | location |

ITEMS

| id | title | location |

Here's my SQL...

SELECT SQL_CALC_FOUND_ROWS *
FROM (
    SELECT *
    FROM items
      UNION
    SELECT * FROM deals
) AS allitemsdeals
INNER JOIN (
    SELECT
        postcodes.*,
        (3959 * acos(cos(radians(custom.lat)) * cos(radians(postcodes.lat)) * cos(radians(custom.lng) - radians(postcodes.lng)) + sin(radians(custom.lat)) * sin(radians(postcodes.lat)))
        ) AS distance
    FROM postcodes
    INNER JOIN postcodes AS custom
    WHERE custom.postcode = ?
) postcodes ON allitemsdeals.location = postcodes.postcode
HAVING distance < 5 ORDER BY id LIMIT ? OFFSET ?

I'm having a problem with my the join on this. The query works but it takes about a minute! :(

Any help would be greatly appreciated! Thanks! :)

wally
  • 3,492
  • 25
  • 31
Goldberg
  • 31
  • 3
  • ST_Within might speed it up but without looking at your explain output it's guess work. Please update your question, instead of posting PHP, post the sql query that actually gets executed. and also indicate what your tables are like and what their row counts are – e4c5 Oct 13 '15 at 09:06
  • Thanks for the response. The question has been updated with examples. :) – Goldberg Oct 13 '15 at 16:27
  • would still need the explain output though. – e4c5 Oct 13 '15 at 16:44

1 Answers1

0

Subqueries are killing your performance. Try prepending EXPLAIN on your SELECT and looking at the output - I suspect lots of USING FILESORT and USING (NULL) INDEX stuff. And lots of records examined. Probably some derived tables too...

All those JOIN (SELECT ... statements cause a query to run, the results to be cached (or worse still re-ran for every record on the left side of the JOIN) without any indexing - and everything grinds to a horrid halt.

Finally that SQL_CALC_FOUND_ROWS - don't do it if you don't have to. It causes MySQL to calculate all rows and process all rows just to count them, before applying the LIMIT. You'd be vastly better off reducing the results as much as possible even before the LIMIT - and definitely best off not using SQL_CALC_FOUND_ROWS. (A separate SELECT COUNT(1) would be better, trust me. Been there.)

Solution? Not easy. Start by changing your schema.

First: I'd combine the "deals" and "items" into one large table - use a column to distinguish the types (unless there's lots of other columns I've not got visibility of) because UNION will likely (or definitely?) kill any indexes you have.

Second: That subquery for the distance - oh noes! That should be a straight WHERE clause for calculating the max distance. I'll take a stab at optimising it if you want - but without an SQLfiddle.com or something to play with, it's a bit much to start replicating your schema and data in my free time. (If you setup an SQLfiddle with it - I'll happily have a poke.)

Thirdly: I'd consider starting again with the long/lat data using MySQL's spatial functionality. There's lots of documentation available:

This is some cool stuff - the kind of data I've wanted to have an excuse to play with during (paid!) hours myself for a very long time. I'm interested to see what solution you end up with.

As I say - at the very least I'll happily have a look over the next 72 hours if you provide an SQLfiddle (assuming somebody else doesn't provide a better solution first).

Community
  • 1
  • 1
wally
  • 3,492
  • 25
  • 31
  • Good answer. but I think most of the answers in 'Fastest way to find ...' you have linked to have been superseded by st_within – e4c5 Oct 13 '15 at 16:53
  • The "fastest" way to find the 5 nearest cannot use a simple index -- you are stuck with a full table scan, or perhaps a 1-dimensional scan (eg `lat between ... and ...`). For fast lookup in arbitrarily large datasets, see http://mysql.rjweb.org/doc.php/latlng . – Rick James Oct 16 '15 at 02:01