Anonymous, bureaucrat, administrator
7,528
edits
Changes
no edit summary
* Timing (with precision searching): 2 seconds average
* Timing (with precision searching and custom index): 0.59 seconds average
= 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.
= Enabling Precision Searching =
N.B. A precision match means that case sensitivity is also adhered to. i.e. "bxp softwareSoftware" is not the same as "business expressbxp software"
{| class="wikitable"
|-
! id
! first_name
! last_name
! 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
|-
| Evan 7 || Marg || Grasmick | Zigomalas | Product 1 | Cap Gemini America | 5 Binney Wrangle Hill Auto Auct & Slvg || 7457 Cowl St #70 || Abbey Bargate Ward
|-
| France 8 || Laquita || Hisaw | Andrade | Product 1 | Elliott, John W Esq | 8 Moor Place In Communications Inc || 20 Gloucester Pl #96 || East Southbourne and Tuckton WChirton Ward
|-
| Ulysses 9 || Lura || Manzella | Mcwalters | Product 1 | Mcmahan| 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, Ben L | 505 Exeter Rd 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. {| Hawerby cum Beesbyclass="wikitable"
|-
! id! last_name | Tyisha -| Veness 3 | Product 1 | Champagne Room Andrade| 5396 Forth Street -| Greets Green and Lyng Ward7 || Grasmick
|-
| Eric 8 | Rampy | Product 1 | Thompson, Michael C Esq | 9472 Lind St | DesboroughHisaw
|-
| Marg | Grasmick | Product 1 | Wrangle Hill Auto Auct & Slvg | 7457 Cowl St #70 | Bargate WardLacey
|-
| Laquita 9 | Hisaw | Product 1 | In Communications Inc | 20 Gloucester Pl #96 | Chirton WardManzella
|-
| Lura 4 | Manzella | Product 1 Mcwalters| Bizerba Usa Inc -| 929 Augustine St 6 | Staple Hill Ward| 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)