[GRASS-user] the performance of displaying vector attributes - with MySQL no difference!!

[Zbigniew Perski]

On Mon, 2006-11-13 at 11:51 +1300, Brent Wood wrote:
> Brad Douglas wrote:
> > On Sun, 2006-11-12 at 20:12 +0100, perski at uranos.cto.us.edu.pl wrote:
> >   
> >> I spent some time on it during last days and I sucesfully introduced MySQL database to grass.
> >> Unfortunately during displaying poits with colors from rgb columns THERE IS NO DIFFERENCE in performance comparing to dbf engine. After adding indexes to grassrgb column is still the same.
> >>
> >> Any other ideas hoe to speed up this task?
> >>     
> >
> > There are a number of things that can be done to speed up database
> > queries.  I suggest anyone who is using a true SQL database on the
> > backend, learn how to successfully normalize the database.  Here are a
> > few links about database normalization:
> >
> > http://en.wikipedia.org/wiki/Database_normalization
> > http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html
> >
> > Different databases take different approaches to administration and
> > performance optimizations, but knowledge of SQL92 generally enough for
> > successful normalization.
> >
> > I suspect that other columns in the query are the source of the slow
> > down.
> I suggest normalisation is a technique normally used for robust, largely 
> error free database implementations. Once a properly normalised model 
> has been devised, you then de-normalise the actual implementation as 
> appropriate to improve performance.
> 
> You might try PostGIS, though I'm not sure if you still need to actually 
> import the geometries into GRASS or can just utilise the external db. 
> Postgres has largely caught up with MySQL for performance, and the 
> PostGIS extension, last time I looked, was way ahead of MySQL spatial in 
> functionality.
> 
> Also, are you sure you properly indexed the relevant data columns in MySQL?

Thanks a lot for all answers. Well, in fact I am not 100% sure if I
indexed it properly. CAT indexes have been created by default and then I
used command:
CREATE INDEX rgb ON my_database_name (grassrgb);
I will look on the optimalization stuff now.

thanks a lot

Zbigniew 


> 
> Cheers,
>   
>   Brent Wood
>