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

[Jukka Rahkonen]

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-