cartesian co-ordinates from a table 1

[BB101]

got this:

CREATE TABLE `dat_question` ( 
  `q` int(11) NOT NULL auto_increment, 
  `category_id` int(11) NOT NULL default '0', 
  `user_id` int(11) default '0', 
  `datetime` int(11) NOT NULL default '0', 
  `x` int(11) NOT NULL default '0', 
  `y` int(11) NOT NULL default '0', 
  `x1` int(11) NOT NULL default '0', 
  `x2` int(11) NOT NULL default '0', 
  `y1` int(11) NOT NULL default '0', 
  `y2` int(11) NOT NULL default '0', 
  `r` int(11) NOT NULL default '0', 
  `views` int(11) NOT NULL default '0', 
  `host` varchar(128) NOT NULL default '', 
  `name` varchar(64) NOT NULL default '', 
  `text` text NOT NULL, 
  `lines` int(11) NOT NULL default '0', 
  `active` tinyint(4) NOT NULL default '0', 
  PRIMARY KEY  (`q`), 
  KEY `x` (`x`), 
  KEY `y` (`y`), 
  KEY `category_id` (`category_id`), 
  KEY `user_id` (`user_id`), 
  KEY `datetime` (`datetime`), 
  KEY `active` (`active`), 
  KEY `xs` (`x1`,`x2`), 
  KEY `ys` (`y1`,`y2`) 
) TYPE=MyISAM AUTO_INCREMENT=1; 

SELECT *, SQRT(POW(x-$x,2)+POW(y-$y,2)) as distance 
FROM `dat_question` 
  WHERE x1 <= $x <= x2 
    AND y1 <= $y <= y2 
    AND SQRT(POW(x-$x,2)+POW(y-$y,2)) <= r 

EXPLAIN: 
table        type  possible_keys  key  key_len  ref  rows   Extra   
dat_question ALL   NULL           NULL NULL     NULL 439400 Using where

However, it doesn't use the index. I've tried all sorts of things, grouping indexes, splitting to individual indexes and nothing works. Can anyone advise? Thanks

--BB

 

[TonyGroves]

First, you would need to rephrase that query as follows:
[tt]
SELECT *, SQRT(POW(x-$x,2)+POW(y-$y,2)) as distance
FROM `dat_question`
WHERE
x1 <= $x AND x2 > $x
AND y1 <= $y AND y2 > $y
AND r > SQRT(POW(x-$x,2)+POW(y-$y,2))
[/tt]

Then, the only indexes it could use are ones on fields x1, x2,
y1, y2, and r. You already have indexes on x1 and
y1, and it should now use those. If that's not enough, you could create 3
more indexes, on x2, y2, and r.


-----
ALTER world DROP injustice, ADD peace;

 

[BB101]

A star for you my man I've gotta ask, why? I have been playing with the indexes for a while, so i know its your query that is using them. What makes it realise it can use the index?

--BB

 

[TonyGroves]

I don't know why it wasn't using the indexes for x1 and y1, but the expression x1<=$x<=x2, though legal, is (presumably) wrong.

The expression is evaluated as (x1<=$x)<=x2. x1<=$x yields either 1 or 0 (true or false), so x2 ends up being compared to 1 or 0, which I presume is not what you want.

P.S. I notice a few mistakes in the revised query - every > should be replaced with >=.


-----
ALTER world DROP injustice, ADD peace;