Precision Searching
Contents
1 Background
On large database tables like searching can cause massive speed overhead for no appreciable useful functionality. Like matching is what gives us fuzzy searching.
A quick example to explain. If you have an id field with for example 8 digits or more in it. You have 1000 records. You enter in 123456 as your search criteria, the database server will search for every combo of that Id, e.g. 123456XX, X123456X and XX123456. So that's 3 different combinations on 1000 = 3000 searches. Fuzzy is helpful when searching names, but not really necessary when searching Id fields. Instead you should consider making the search an "exact" search. This means that the Id number is found exactly or not at all.
Here are some record statistics from bxp
- Database: 1.2 million customer records
- Timing (with fuzzy searching): 16.2 seconds average
- Timing (with precision searching): 2 seconds average
- Timing (with precision searching and custom index): 0.59 seconds average
2 Database query
In the background the system creates a database query to do the lookup. In a normal fuzzy search the query will have in the where clause "Where strCDA_X_field_0_0 like '%yourValue%'"
After turning on precision searching the query will now read as "Where strCDA_X_field_0_0 = 'yourValue'"
The % wildcard charcters are removed and the like statement is changed to a precision match.
3 Enabling Precision Searching
To enable precision searching for any client:
1. Main Menu > Database Management > Field Mapping > Select the campaign > identify the full field name of the column e.g strCDA_X_field_0_0
2. Main Menu > Database Management > Database – Primary Management > Campaign – Edit > Select the campaign > Advanced Options > Precision Search Fields
3. In this box put the field identified in item 1.
Multiple fields may be made precise using this method, simply add multiple fields separated by commas to this same box.
N.B. A precision match means that case sensitivity is also adhered to. i.e. "bxp Software" is not the same as "bxp software"
4 Indexes
An index is a special feature of a database which has the ability to vastly increase the speed of searches at the cost of space. Imagine some data.
| id | first_name | last_name | primary interest | company_name | address | city |
|---|---|---|---|---|---|---|
| 1 | Emma | Lacey | Product 1 | Alan D Rosenburg Cpa Pc | 14 Taylor St | St. Stephens Ward |
| 2 | Evan | Zigomalas | Product 1 | Cap Gemini America | 5 Binney St | Abbey Ward |
| 3 | France | Andrade | Product 1 | Elliott, John W Esq | 8 Moor Place | East Southbourne and Tuckton W |
| 4 | Ulysses | Mcwalters | Product 1 | Mcmahan, Ben L | 505 Exeter Rd | Hawerby cum Beesby |
| 5 | Tyisha | Veness | Product 1 | Champagne Room | 5396 Forth Street | Greets Green and Lyng Ward |
| 6 | Eric | Rampy | Product 1 | Thompson, Michael C Esq | 9472 Lind St | Desborough |
| 7 | Marg | Grasmick | Product 1 | Wrangle Hill Auto Auct & Slvg | 7457 Cowl St #70 | Bargate Ward |
| 8 | Laquita | Hisaw | Product 1 | In Communications Inc | 20 Gloucester Pl #96 | Chirton Ward |
| 9 | Lura | Manzella | Product 1 | Bizerba Usa Inc | 929 Augustine St | Staple Hill Ward |
To search by the surname is a difficult process of checking each one. An index would create an ordered list. Andrade, Grasmick, Hisaw, Lacey, Manzella, Mcwalters, Rampy, Veness, Zigomalas. This would make searching far easier, but the duplication of data is a significant overhead as a mini dataset is created.
| id | last_name |
|---|---|
| 3 | Andrade |
| 7 | Grasmick |
| 8 | Hisaw |
| 1 | Lacey |
| 9 | Manzella |
| 4 | Mcwalters |
| 6 | Rampy |
| 5 | Veness |
| 2 | Zigomalas |
Indexes can also be grouped, e.g. firstname and surname. Indexes for large data sets are limited to the primary searching fields and the most commonly grouped fields.
It is often sensible to create an index on an "Id" field or an identification field that contains values from another system. Indexing these fields is always useful for speed.
bxp has numerous default indexes added but in custom forms, there is no way to know which field to add indexes to.
If you need to enable a custom index, please contact your business development manager to have the field indexed for you. (support@bxpsoftware.com)