Field Types
The Rules Engine must craft SQL queries for each unique search. Due to limitations in SQL, queries with an exact string (such as a name) are highly inefficient compared to searching by an indexable value (such as a date). Due to this, the Rules Engine can only filter by the following Profile Field Types:
- Select
- Date
- Linked
- Hierarchy
The Rules Engine cannot filter by the following Profile Field Types:
- Text
Why These Limitations?
Keep in mind that this is a highly simplified example, and the technical details are vastly different (and far more complex) in the real world.
Non-Filterable Fields
Things like a name are unique. If you are doing a search on a name, the system has to grab all the records that have that exact value. In order to locate a particular value, the search has to look through every single record to find the ones that match. This is why having each name as a select value wouldn't do you any good. Because there aren't 5 or 50 set values, there's just a unique collection of unique values.
This requires the system to find those values, by essentially searching every single record, and checking. Just to find the very short name "Tom", the search has to:
- Look at every single user in the system, and determine: Does this name start with the letter T?
- If yes, then look at the second letter. Is it an O?
- If yes, then look at the third letter. Is it an M?
That's 3 lookups back and forth to the database in order to find a single user.
Filterable Fields
In contrast, values that are selected from a predefined list, a.k.a. a select profile field, can have an index built in the background that lets you search for all records with a particular value by simply doing an index look-up. For example, if you are searching by state, you know that there are going to be exactly 50 values. So the system can (ahead of time) make an index where each state has 1000 records, or however, many people work in each state. If you want to do a search for all users from Montana, the Search says:
- Give me all records that are indexed with state = Montana.
That takes a single call to the database, and can return potentially thousands of results.
Because there happen to be 50 states, search can immediately discard 98% of all the records, and simply return that 2% (or 1/50th) of the values. As you can imagine, this is far faster than scanning through every name record multiple times.
If you have 10,000 employees, and put their names in a Select field, the index would be 10,000 records long. Determining which index value to use would take just as long as just searching directly through the database. It would actually add a step, because a direct lookup wouldn't have to take the index value, and then go do a 2nd lookup to find the actual records.