[TIP] Partial Index in Valentina Database.

Today one of user have asked if Valentina supports partial index. Answer is YES OF COURSE, you can easy create a partial index using Table Methods.

Partial index of a Table field means that index stores values of column partially. You can reduce data
* horisontally – use e.g. only first N chars of a VarChar field
* vertically – use values of not all records.

Example 1 – Partial Horizontally

Let’s create VarChar Table Method, which takes only first 5 chars of URL.
We make this method to be indexed.

fldURL_5 = tbl.CreateVarChar( “fldURL_5”, 2044, fIndexed, “left(fldURL, 5)” )

Now you can do effective search as:  … WHERE fldURL_5 LIKE ‘A%’
Really, if average length of URL is 100 chars, then index becomes at least 20 times less.
Speed therefore is increased also.

Example 2 – Partial Vertically

RULE: NULL values of a field are not stored in the index.
Using this rule you can get idea how to implement  partial vertically index. It need make Table Method, which returns NULL for all values that should be skipped.

fldPrice_100_200 = tbl.CreateDouble( “fldPrice”, fIndexed, “if( fldPrice between (100,200), fldPrice, NULL)” )

Published by

Ruslan Zasukhin

VP Engineering and New Technology Paradigma Software, Inc