Hi Ed, i tried to create this table
CREATE TABLE `rectangle` ( `myname` char(5) NOT NULL default '', `minx` tinyint(3) unsigned default '0', `miny` tinyint(3) unsigned default '0', `maxx` tinyint(3) unsigned default '0', `maxy` tinyint(3) unsigned default '0', `miscfield1` tinyint(3) unsigned NOT NULL default '0', `miscfield2` tinyint(3) unsigned NOT NULL default '0', UNIQUE KEY `myindex` (`maxx`,`maxy`,`minx`,`miny`,`myname`) ) TYPE=MyISAM and execute this query explain select myname from rectangle where minx<=2 and maxx>=2 and miny<=3 and maxy>=3; result: table,type,possible_keys,key,key_len,ref,rows,Extra rectangle,range,myindex,myindex,6,NULL,4,Using where; Using index perhaps you have some other field as primary key.. adding an index on those four field only (top,left,bottom,right) wouldnt make much use.. try to create an index on those four plus one (or more) field that represent your record as unique as possible i hope this help, cmiiw.. if ther's still a problem maybe you can mail me your table structure regards -leo- ----- Original Message ----- From: Ed McNierney To: [EMAIL PROTECTED] Sent: Thursday, November 20, 2003 11:22 AM Subject: Cannot find an index that will be used for SELECT I'm completely stumped trying to create any index MySQL will use at all for my SELECT. I've got a table with four columns describing the upper-right and lower-left coordinates of a rectangle - RIGHT, LEFT (max X, min X), TOP, BOTTOM (max Y, min Y). I have an X, Y coordinate and I want to select all rows for which the X, Y point is "inside" the rectangle. In other words: SELECT * FROM ROWS WHERE X <= RIGHT AND X >= LEFT AND Y >= BOTTOM AND Y <= TOP (using BETWEEN didn't make any difference). I have tried indexes on RIGHT, LEFT, TOP, BOTTOM, RIGHT+LEFT, TOP+BOTTOM, and RIGHT+LEFT+TOP+BOTTOM and none of them get used. All fields are FLOAT. I can't figure out how to get any index to be used, nor how to restructure my query to improve things. Thanks!
