Changes

We had an incident recently which reminded us to be careful to use the appropriate search method, depending on the field being searched.

The truth is that the most common type of database search is like or fuzzy searching, but this kind of exercise can cause a significant performance overhead, especially when your database is a large one.

For example, your records include an id field with eight digits and you have a total of 1000 records. If you then enter six digits, such as 123456 as your search criteria, the server will hunt for every combination of that id, that is 123456XX, X123456X and XX123456. In other words you have three different combinations for each of your 1000 records, requiring a total of 3000 searches. Ok, 3000 is no big deal but the situation becomes much, much worse when you ramp up the numbers. Try the math when you enter five digits in a twelve digit field with a database containing 2.5 million records; now you have an issue.

So, whilst fuzzy search is useful when searching names for example, it is less helpful for numeric fields.