Multi column index vs compact single column index

[Ashkrit Sharma]

I want to solve some hierarchy based query and i am considering 2 ways to create index

• Multi column index ( Country, State,City,Zip)

In this option query will always add filter to index column and also maintain the column order like country , country+state, country+state+city , ....

   

• Single generic column ( Country#State#City#Zip)

This is more inspired from dynamo DB. Single generic column having all the value like USA#CA#San Francisco#94016 only for indexing purpose.  Query will be always begins with query yo take advantage of index. Sample query will look like key like 'USA%' or key like 'USA#CA%' etc

Looking at explain plan confirms that H2 is doing range search for begins with query.

I am aware of the storage overhead due to data duplicate in 2nd approach. 

My question is which type of index is better and why ? How does this impact when data type of columns are different ?

[Evgenij Ryazanov]

Hello.

Queries like SELECT * FROM SOME_TABLE WHERE COUNTRY = something AND STATE = something and similar will be able to use a normal multi-column index if first columns of this index are COUNTRY and STATE. There is no reason for additional weird indexed computed combined column, such column will only increase size of you database.