MySQL Query Slow when using Order By with function to calculate distance between two points (long, lat)

Question

I have a query in MySQL which runs a stored function on each row of a table and then orders the rows by the result of the function before returning the first 10 rows.

SELECT rowId, MyFunction(x, y, constX, constY) AS funResult
FROM myTable
ORDER BY funResult DESC
LIMIT 10

The problem is that it takes several seconds to run on a table with 10,000 rows which is much too slow. The result of the function can't be computed and stored as another row in the table because it takes a constant which is given by PHP and is different each time the query is run.

The speed of the function itself is not the problem, since removing ORDER BY funResult DESC LIMIT 10 means that the query runs in less than 0.01 seconds.

The problem must be sorting the rows - is there any way this can be done faster, considering the fact that only the first 10 rows are needed?

Update

The simplified function being used calculates the distance between each row and a specified point (where LAT_B and LON_B are constants dependent on the query):

CREATE FUNCTION MyFunction(LAT_A float, LON_A float, LAT_B float, LON_B float)
RETURNS double
DETERMINISTIC
BEGIN

DECLARE tempCalc DOUBLE;
SET tempCalc = 3956 * 2 * ASIN(SQRT( POWER(SIN((LAT_A -abs( LAT_B)) * pi()/180 / 2),2)    
    + COS(LAT_A * pi()/180 ) * COS( abs(LAT_B) *  pi()/180)
    * POWER(SIN((LON_A - LON_B)
    * pi()/180 / 2), 2) ));

RETURN tempCalc;

END

Answer 1

Options:

1. Incorporate sorting within your stored procedure definition/logic. If your calling SQL select within your stored procedure perform the sort and limit there. - This means you will not be producing 10,000 rows in the stored procedure, just to resort them. Also, if table has indexes original sort within SQL select may be much faster.

2. Verify that indexing is used within your table. - Indexes will cause your sorts to be performed quicker when selecting on the table.

Please provide us with the function definition, it would be easier to additionally help you.

Finally, try to move your order by and limit directly within your function as opposed to doing them later. Your function can return the 10 results directly sorted and ready. If you want, make two functions - one that returns the full results and one that returns them limited and sorted.

Update:

After seeing your function it becomes apparent that your trying to order by a calculated value. Ordering by calculated values is extremely slow as also mentioned in:

• https://stackoverflow.com/a/3401611/1688441
• MySQL-Performance when ordering on calculated column

I am trying to think how you could "pre-process/order" your data based on col1 or col2 in order to speed up the ultimate ordering of your results. If col1 and col2 are columns of the table, and funResult is a mathematical function that can be graphed one of the two has a higher effect on the function return value....

Finally, if col1 and col2 are columns of myTable, you do not need to use a stored function but could query with, but this wouldn't make a large difference...Your main problem is ordering by a calculated function:

SELECT rowId, ((col1-INPUT_CONST)*2)+(col2*3) AS funResult
FROM myTable
ORDER BY funResult DESC
LIMIT 10

Update 2:

After digging for the problem of sorting be calculated distance I found that this has been asked and solved in a very efficiently at the link below. In relation to sorting by a calculated value, as your sorting by a calculated value it is inherently slow. See the following two links for additional help:

• http://www.mooreds.com/wordpress/archives/547 - "Optimizing a distance calculation in a mysql query "
• Fastest Way to Find Distance Between Two Lat/Long Points .

Finally, the closest that comes to your answer is this: https://stackoverflow.com/a/4180065/1688441

Answer 2

Expanding your function:

MyFunction(col1, col2, constant) = (col1 - constant) * 2.0 + col2 * 3.0
                                 = 2*col1 + 3*col2 - 2*constant

Therefore ordering by MyFunction(col1, col2, constant) is equivalent to ordering by 2*col1 + 3*col2 irrespective of the constant supplied. Therefore, you can cache the result of that calculation in a new, indexed column:

ALTER TABLE myTable
  ADD COLUMN tmpResult FLOAT,
  ADD INDEX (tmpResult);

CREATE TRIGGER ins BEFORE INSERT ON myTable FOR EACH ROW
  SET NEW.tmpResult := 2*NEW.col1 + 3*NEW.col2;

CREATE TRIGGER upd BEFORE UPDATE ON myTable FOR EACH ROW
  SET NEW.tmpResult := 2*NEW.col1 + 3*NEW.col2;

UPDATE myTable SET tmpResult = 2*col1 + 3*col2;

Then your SELECT becomes:

SELECT   rowId, tmpResult - 2*constant AS funResult
FROM     myTable
ORDER BY tmpResult DESC
LIMIT    10