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 <https://en.wikipedia.org/wiki/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 ? -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/c509917a-e45a-4e28-90b6-308999e624e2%40googlegroups.com.
