SQL optimisation

I have this query on my database, it's basically pulling the ones that is within 10 miles radius from the coordinate.

SELECT id 
  FROM business 
 WHERE  ( coordinates!='' 
   AND getDistance('-2.1032155,49.1801863', coordinates)<10 ) 
   AND id NOT IN ('6', '4118') ORDER BY rand() LIMIT 0,5

when I profile this query, I get this: JOIN SIZE: 3956 (BAD) USING TEMPORARY (BAD) USINGI FILESORT (BAD)

C开发者_Python百科an you guys help me optimising this query?

Thanks in advance

---

You will always need FILESORT (sorting the resulting data in temporary memory) as you "order by" something that is not indexed (rand()).

Depending on the optimizing capabilities of your DBMS, you might be better of using "AND id != '6' AND id != '4118'" instead of a "NOT IN" clause.

You should always state the fixed information first in queries, although this depends on the capabilities of the query optimizer as well. Also, the criteria should align with an index, meaning that the order of appearance of criteria should be the same as in the index you want your DMBS to use. There's usually an option to state which index to use (the keyword is "Index hint"), but most DMBS know best what index to use in most cases anway (guessing from the query itself).

And you will never get around USING TEMPORARY while you are using a criterium that is generated at query runtime (your function).