Why is a search with a leading wildcard such as *ing or *error* especially expensive?

Because the index is sorted by the beginning of each term: a known prefix lets the engine jump straight to matching terms, but a leading wildcard forces it to examine every term in the index.

* A known prefix is a jump; a leading wildcard is a full scan. *

An inverted index works like a dictionary's word list. For fail* the engine goes to the spot where terms starting with fail begin and reads until they end, which is quick. For *ing there is no starting spot: any term anywhere might end in "ing", so every term has to be checked. On an index holding billions of log events, that turns a millisecond lookup into a full scan.

The same rule applies in Splunk's advice to search fatal_error rather than *error*, and in SQL, where LIKE 'abc%' can use an index but LIKE '%abc' cannot. The fix is to search a more specific term, or, if suffix searches are needed often, to index the data so that the part you search for becomes its own field.

Go deeper:

• Inverted index — Wikipedia — the data structure that makes prefix lookups cheap.