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

[Rahkonen Jukka (Tike)]

Hi Even,

Good points and vacuuming is also very important sometimes but but does not suit at all for a creation option. I will think about some text and examples that could be added into SQLite/Spatialite performance hints.

-Jukka-


Even Rouault wrote:

> 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-