[gdal-dev] New option INDEX_COLUMNS for SQLite and GPKG

[Even Rouault]

Hi Jukka,

I'm a bit ambivalent about providing a creation option for that (although that 
would not be an heresy). For a few reasons :
- it is relatively easy to create an attribute index manually (once you know 
the syntax)
- it is not necessarily to create it at layer creation time
- it would potentially apply to all drivers that have a SQL engine behind

But I agree that improving the documentation to advertize the interest of 
indexes and how to create them could be usefull. Perhaps you would want to 
propose a modified version of the HTML page ? (I think that the GPKG one could 
just point to the relevant section of the SQLite one, to avoid doc 
duplication)

Even

> Hi,
> 
> I guess that both SQLite/Spatialite and especially OGC Geopackage will be
> used by people who consider them more like file formats than as datebases.
> Such users may not know the power of indexes and they do not necessarily
> know anything about SQL and such.
> 
> How about writing a few lines about the power and importance of indexes
> into the SQLite and GPKG driver pages? There could at least be on example
> about how to create a new index with ogrinfo, which must be a secret even
> for many advanced GDAL users. Better though, from user point of view,
> would be to implement a new layer creation option. I see a very similar
> case in SQLite/Spatialite
> "COMPRESS_COLUMNS=column_name1[,column_name2, ...]: (Starting with GDAL
> 1.10.0) A list of (String) columns that must be compressed"
> The new -lco might be
> "INDEX_COLUMNS=column_name1[,column_name2, ...]: (Starting with GDAL 2.0) A
> list of columns that will be indexed".
> 
> I am not sure if -lco should somehow support also composite indexes.
> Perhaps users who know what those (and unique indexes) mean can also use
> SQL and create them with ogrinfo if they can see an example in the
> documentation. Thus -lco could be made to accept only one column per
> index. And index name in the db could be set automatically into something
> like
> "layer_name_column_name_idx". The SQL that this -lco should fire is simply
> 
> CREATE INDEX "table_name_column_name_idx"
> ON "table_name" ("column_name");
> 
> -Jukka Rahkonen-
> 
> _______________________________________________
> gdal-dev mailing list
> gdal-dev at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/gdal-dev

-- 
Geospatial professional services
http://even.rouault.free.fr/services.html