MySQL-Performance when ordering on calculated column

Question

Given is a table with 300'000 test-records.

I need to do a select like this:

SELECT (SQRT(POWER(ABS(posts.latitude-$lat),2)+POWER(ABS(posts.longitude-$lng),2))) AS distance
FROM table
ORDER BY distance
LIMIT 0,50

where $lat and $lng changes for every request. How could I possibly speed up such queries?

Thanks.

Multiplication doesn't change the ordering! Order by a and order descending if $userParam is negative and u'll be fine. — palindrom, Mar 08 '13 at 14:34
you are right, it was a pretty bad example - I'll change it to make it more clear. — Raphael Jeger, Mar 08 '13 at 14:36
I would suggest to make a simple select and then get and order the result data. — Ander2, Mar 08 '13 at 14:37
@RaphaelJeger don't sort the data on the query. Run the query, get the data on an array and the sort the array with `sort`. — Ander2, Mar 08 '13 at 15:03
@Ander2 Interesting... really? So I'd have to put 300'000 records in my array? And (hopefully) that will grow even bigger... and then sort in PHP? Is that really faster? I'll have to test... — Raphael Jeger, Mar 08 '13 at 15:06
@Ander2 problem is if I could sort in SQL I could use the limit (adjusted the example) because I don't need to display 300'000 records but need the ordered top 50 f.e. — Raphael Jeger, Mar 08 '13 at 15:33
Try the geometry datatypes and a spatial index (this means the table has to be in MyISAM engine). — ypercubeᵀᴹ, Mar 09 '13 at 14:22

score 1 · Answer 1 · answered Mar 09 '13 at 14:14

1

Actually you cannot optimize that query.

You are sorting the result using a calculated value, so you cannot use an index. If you use explain you could see how your query in being executed, and using temporary will be present in the extra column, which means that all the data from your query is being stored on a temporary table in which the ordering is performed.

It doesn't matter if you only want the first 50 matches in the query, it has first to get all the data, dump it into a temporary table, sort the result in that table and then return to you the first 50 matches.

As you can suppose, this is a time and memory consuming operation.

So you best option is to place an index in the table to get all the rows you need as fast as you can and then process them with php to get the data you need.

By the way, have a look to MySQL optimization guide.

answered Mar 09 '13 at 14:14

Ander2

5,569
2
23
42

1

So, your suggestion is to add an index? Or send the 300k rows to PHP (or whatever the OP is using, there are many languages out there, not only php) and do the calculations there? – ypercubeᵀᴹ Mar 09 '13 at 14:24
The suggestion is to add one (or more) index to filter as much data as it could, and then send all that data to PHP and do the calculation and sorting there (using arrays). – Ander2 Mar 09 '13 at 14:28
What query are you suggesting to use for the filtering? – ypercubeᵀᴹ Mar 09 '13 at 14:40
Nothing for this query ( there are no `where` conditions ). – Ander2 Mar 09 '13 at 14:43
So, if he keeps this query, which will return maximum 50 rows, what is the need for the PHP sorting? – ypercubeᵀᴹ Mar 09 '13 at 14:44
Read the answer again. Before he gets the 50 rows, mysql has to get ALL the rows, store them in a temporary table, do the sorting and then return the first 50 ones. Performing the sorting and processing in PHP will be faster. – Ander2 Mar 09 '13 at 14:46
I agree with that, 100% (that this MySQL query will be slow) and for your reasoing. But what you propose as an alternative is not clear. Not clear at all. – ypercubeᵀᴹ Mar 09 '13 at 14:54
Well, the query cannot be optimized, so his only chance is to process the data himself. From my experience, sorting big tables without indexes in mysql could be very very slow. – Ander2 Mar 09 '13 at 14:57
3

Well, I can't really understand why you think that PHP will do the sorting faster. It's going to be equally slow. PHP cannot magically sort 300k rows. – ypercubeᵀᴹ Mar 09 '13 at 15:00
Just made a local example with an int's array and it takes less than a second ordering 300.000 elements. Fiddle example: http://phpfiddle.org/main/code/c6v-hyx – Ander2 Mar 09 '13 at 15:22

score -1 · Answer 2 · answered Mar 08 '13 at 14:38

-1

Make the column for $userParam an index, that way the query will perform faster. Or you could create an indexed view: http://www.codeproject.com/Articles/199058/SQL-Server-Indexed-Views-Speed-Up-Your-Select-Quer

Hope this helps

answered Mar 08 '13 at 14:38

Crowlix

1,269
9
19

Sure did I index properly but your other part of the answer seems to be MS-SQL? – Raphael Jeger Mar 08 '13 at 16:38

MySQL-Performance when ordering on calculated column

2 Answers2

Linked