Subject Re: Need to optimize this query for my benchmark tools against Firebird / Mysql / Sqlite3
Author chris.waldmann
--- In firebird-support@yahoogroups.com, Vander Clock Stephane <svanderclock@...> wrote:
>
> Dear Chris,
>
> i m subscrided to http://tech.groups.yahoo.com/group/Firebird-Architect/
> but i don't see any thread speaking about rtree ?
>
> the thing we are trying to do is a very simple query like :
>
> Select xxx from tableA where FieldA > xxx and FieldA < yyyy and FieldB >
> www ....
>
> this kind of query is very common nowadays ... in little table (< 1
> millions rows) the perf are acceptable but as soon as the table grow,
> for exemple on big table (+20 000 000 rows), just doing where FieldA >
> xxx take a very (very) long time to return (around 10 secondes to fews
> minutes)
>
> on the same data, on the Sqlite3 rtree index, it's take 10 ms!
>
> i already have done a demo to benchmark the queries :
> http://sourceforge.net/projects/alcinoe/files/alsqlbenchmark/1.00/ALSQLBenchmark_1_00.zip/download
>
> this tools will create the table on Firebird or on Sqlite3 using Rtree
> (on mysql it's also possible but i don't know how to write the SQL using
> their GIS), and permit you to do some benchmark ....
>
> st├ęphane
>
>
>

Hello St├ęphane

Many thanks for your info. I will look at it and think about and start (soon ?) a thread in Firebird-Architect.

Christian