SOQL query for text in the middle of text

Question

Usually in SQL when you do a search on the lines of

... where blah like 'tony%' 

You can index blah column and then get a B Tree search.

When you search for

'%tony'

You don't get a B tree search even if you index the column and you can get a table scan unless the database indexes it another way which varies from database to database.

What way does it work in SOQL land?

1. How do we specify to index a column?
2. How do we specify the type of index?
3. How do we specifcally deal with the search of type '%tony' and avoid table scan?

Answer 1

SOQL has VERY different rules than SQL for query efficiency and selectiveness. Specifically, you need to avoid the dreaded "non-selective query" errors once your datasets get large (over 100k records total) and your queries don't meet the stated selectivity requirement of

10% of the records for the first million records and less than 5% of the records after the first million records, up to a maximum of 333,000 records.

This document is the starting point for understanding selectivity (and has a number of lesser known big data gems, like you can file a case to add a custom index).

It's non-specific about the use of wildcards, sadly, but from experience I vaguely recall that wildcard searches do help with the selectivity requirements. I cannot speak for leading wildcards and couldn't find a good reference source on them, so you may need to file a case to get an answer.

Answer 2

When in doubt - discard the "cloud magic" and think about it as an Oracle database ;)

If the text field's length is less or equal 255 characters (text/textarea type) you can use it in WHERE clause or a report filter. The performance of LIKE '%tony%' won't be great but should still be acceptable. Most common trick to slap an index on such field is to mark it as external id (or unique field but I doubt your business requirement allows that). As long as you won't use it as actual external id (in upsert operations etc) it won't change how does your application work.

When you exhaust limit of ext. ids on an object -as jkraybill writes - you can always ask SF support for more ext.ids or simply an index on given field.

If the field is a long text area (CLOB in normal databases) - you can't filter on it in WHERE or reports, so problem solved ;)

If you worry about such performance stuff there's high chance you're doing it wrong. Maybe a SOSL search query would be a better option. Maybe a trigger that sets some helper flags on the record? Maybe even a solution that involves record tags?