[postgis-users] Re: MySQL 4.1 GIS Functionality

[David Blasby]

I think these are the reason for MySQL's slow select performance is:

1. Entire GIS data is in the index.  This means there are significantly 
more disk reads for a scan of the index.  You might be able to get 
several 100 bounding boxes in a single disk page, but less than one per 
page if you're also storing the geometry.

2. MySQL doesnt store the Bounding Box with the geometry.  For a 
large-area select (that doesnt use the index) you will have to read the 
entire geometry column from the disk, then analyse the geometry to find 
its bounding box, then check to see if matches the search criteria. 
This is for *every* geometry and *every* select.  This is the main 
reason why the large area select testing showed mysql to be very much 
slower than PostGIS.

But, I am just guessing - I'm not 100% sure how MySQL writes/reads its 
index.

 >You could also enable MySQL's query cache, in which case the second and
 >subsequent execution of a query should yield almost instantaneous
 >results. :-)

I dont tend to throw the same exact query to the database repeatedly, so 
I dont think this will help me.


As for the MBR_touches() comments from paul, I think you'll confuse 
people if you offer much more than a "overlaps" and "disjoint" operator.

For one thing, the MRB_*() function should hold a relationship like:

MRB_<ogc function>(a,b) is true iff
   <ogc function>( envelope(a), envelope(b) ) is true

OR (subtly different):

MRB_<ogc function>(a,b) is false only if
   <ogc function>( a,b ) is false

MRB_<ogc function>(a,b) is true if
   <ogc function>( a,b ) is likely

Plus, you'll find most of the OGC operators are subtly different from 
what you might naively expect from their names.  Also, you'll find 
people doing queries like:

select * from <table> where MBR_touches(a,b) and touches(a,b);

And expect the correct answer.


Do you have any timeframe for when the GIS support in MySQL will include 
innoDB?  I'm holding off using MySQL GIS until this happens.


dave