CODEWITHSUNDEEP
phpmysqlquery-optimization
Kladskull
Kladskull

Reputation: 10762

Mysql not using my indexes

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

Upvotes: 0

Views: 618

Answers (3)

jerryjvl
jerryjvl

Reputation: 20151

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.

Upvotes: 3

Ken Gregg
Ken Gregg

Reputation:

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

Upvotes: 2

gahooa
gahooa

Reputation: 137542

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)

Upvotes: 2

Related Questions