Difference between revisions of "Precision Searching"
From All n One's bxp software Wixi
Philip Lacey (talk | contribs) |
Philip Lacey (talk | contribs) |
||
| Line 44: | Line 44: | ||
|- | |- | ||
! first_name | ! first_name | ||
| − | + | ! last_name | |
| − | + | ! primary interest | |
| − | + | ! company_name | |
| − | + | ! address | |
| − | + | ! city | |
|- | |- | ||
| Emma | Lacey | Product 1 | Alan D Rosenburg Cpa Pc | 14 Taylor St | St. Stephens Ward | | Emma | Lacey | Product 1 | Alan D Rosenburg Cpa Pc | 14 Taylor St | St. Stephens Ward | ||
Revision as of 14:34, 16 July 2015
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
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 "business express"
2 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.
| first_name | last_name | primary interest | company_name | address | city |
|---|---|---|---|---|---|
| Lacey | Product 1 | Alan D Rosenburg Cpa Pc | 14 Taylor St | St. Stephens Ward | |||||
| Zigomalas | Product 1 | Cap Gemini America | 5 Binney St | Abbey Ward | |||||
| Andrade | Product 1 | Elliott, John W Esq | 8 Moor Place | East Southbourne and Tuckton W | |||||
| Mcwalters | Product 1 | Mcmahan, Ben L | 505 Exeter Rd | Hawerby cum Beesby | |||||
| Veness | Product 1 | Champagne Room | 5396 Forth Street | Greets Green and Lyng Ward | |||||
| Rampy | Product 1 | Thompson, Michael C Esq | 9472 Lind St | Desborough | |||||
| Grasmick | Product 1 | Wrangle Hill Auto Auct & Slvg | 7457 Cowl St #70 | Bargate Ward | |||||
| Hisaw | Product 1 | In Communications Inc | 20 Gloucester Pl #96 | Chirton Ward | |||||
| Manzella | Product 1 | Bizerba Usa Inc | 929 Augustine St | Staple Hill Ward |
If you need to enable a custom index, please contact your account manager to have the field indexed for you.
