MySQL Forums
Forum List  »  GIS

unable to get join of two tables to use the spatial index provided
Posted by: Galt Barber
Date: October 09, 2006 04:47PM

I can't get the spatial index to be used in a simple join!

mysql> desc allEstGeo;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| qName | varchar(255) | NO | | | |
| tName | varchar(255) | NO | | | |
| span | geometry | NO | MUL | | |
+-------+--------------+------+-----+---------+-------+
has 1.3 million rows

mysql> desc xrfGeo;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| name | varchar(255) | NO | | | |
| chrom | varchar(255) | NO | | | |
| span | geometry | NO | MUL | | |
+-------+--------------+------+-----+---------+-------+
has 194 thousand rows

Both of these have a spatial index on span.

mysql> explain select qName, name from xrfGeo x, allEstGeo e where Intersects(x.span, e.span);
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| 1 | SIMPLE | x | ALL | span | NULL | NULL | NULL | 194546 | |
| 1 | SIMPLE | e | ALL | span | NULL | NULL | NULL | 1343137 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+

Even using use index(span) or force index(span) in the
from clause does not help.
What is the point of having a spatial index if it can't be used?

Note: in the case of looking up one value it does work:

mysql> explain select qName, tName, asText(span) from allEstGeo where intersects(span,GeomFromText('LINESTRING(26729 102834029,39573 102834029)'));
+----+-------------+-----------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+-------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | allEstGeo | range | span | span | 32 | NULL | 1 | Using where |
+----+-------------+-----------+-------+---------------+------+---------+------+------+-------------+


In case you are curious, each span basically contains two points
defining the ends of a segment on a chromosome. Each table
is a different data track. I am interested in using mysql to
create the intersection between the tracks. i.e. what particular genes
does each EST tag intersect with?

I assume this is SUPPOSED to work.

-Galt

Options: ReplyQuote


Subject
Views
Written By
Posted
unable to get join of two tables to use the spatial index provided
4984
October 09, 2006 04:47PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.