Mysql not using my indexes

Question

I am using 'explain' to view why my query is hitting every single row in my database, and I do not understand why it is doing so.

Could someone take a look and give me a hint what I am missing?

My database is MyISAM, and I am using Mysql 5.1, PHP5

Here is my table:

--
-- Table structure for table `users`
--

CREATE TABLE IF NOT EXISTS `users` (
  `user_id` bigint(20) NOT NULL auto_increment,
  `name` varchar(40) default NULL,
  `city` varchar(90) default NULL,
  `latitude` float NOT NULL default '0',
  `longitude` float NOT NULL default '0',
  PRIMARY KEY  (`user_id`),
  UNIQUE KEY `name` (`name`),
  KEY `Radius Search` (`latitude`,`longitude`),
  KEY `Radius 2` (`longitude`,`latitude`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=38666 ;

Here is my query:

$query =    
"SELECT
    name, city
FROM
    users
WHERE
    (
     (69.1 * (latitude - " . $user->latitude . ")) * 
     (69.1 * (latitude - " . $user->latitude . "))
    ) + ( 
     (69.1 * (longitude - " . $user->longitude . ") * COS(" . $user->latitude . " / 57.3)) * 
     (69.1 * (longitude - " . $user->longitude . ") * COS(" . $user->latitude . " / 57.3))
    ) < " . pow($radius, 2) . " 
ORDER BY 
    (
     (69.1 * (latitude - " . $user->latitude . ")) * 
     (69.1 * (latitude - " . $user->latitude . "))
    ) + ( 
     (69.1 * (longitude - " . $user->longitude . ") * COS(" . $user->latitude . " / 57.3)) * 
     (69.1 * (longitude - " . $user->longitude . ") * COS(" . $user->latitude . " / 57.3))
    ) ASC";

And finally, my explain...

id  select_type  table  type  possible_keys  key  key_len  ref  rows  Extra
1   SIMPLE  users  ALL  NULL  NULL  NULL  NULL  38665  Using where; Using filesort

Answer 1

How exactly is it supposed to use a sorted index on 'latitude' to more efficiently evaluate your where clause? ... When a field is used in an expression in the where clause, indexes typically do not apply.

Moreover, in this case I'm not sure you can even rewrite the clause to make the index apply, which is a little rarer.

Addendum:
Looking at what you are actually trying to do, maybe you can use a fixed-size lat/long box around the users' coordinate, so that you can use BETWEEN clauses on lat and long. Then you can keep the ORDER BY complex as it is now, and cut-off after the 'x' closest other users.

Then you may never notice that the matches are actually in a box rather than a circular radius from the one you are considering.

Answer 2

Most likely it is because you are filtering on an EXPRESSION rather than an indexed column.

MySQL indexes on columns, not expressions. You have a couple of options here:

1. Can you boil down the expression to a fixed value and store it in one or two columns?

If that is not possible, then consider creating a very fast lookup table, and join against it. For example:

CREATE TABLE user_loc 
(
    longitude float(n,m) not null, 
    latitude float(n,m) not null, 
    user_id int unsigned not null
);

Because it will be a tiny fixed width table, you should be able to query it very fast even with a full table scan. Just join against it to get the users you want.

(note: check performance requirements before going to these lengths, as you may not need it at all)

Answer 3

You are not actually comparing anything to latitude or longitude. For indes to work mysql nees to se something to the effect latitude = (or < > between, etc).

Try rewriting the query so it reads

WHERE latitude between $lat_low AND $lat_hi and longitude between $long_low AND $long_hi