Search and the inverted index

Key points

• A leading-wildcard LIKE scans every row and cannot rank anything. It is a filter, not a search engine.
• An inverted index turns the table inside out: for each term it keeps the list of document ids containing it - the postings list.
• Documents pass through an analyzer first: lowercase, split on punctuation, drop stop words, then stem so running also matches run.
• The query runs the same analyzer. Index one way and query another and a document that clearly matches will score exactly zero.
• Matching and ranking are separate. BM25 scores rare terms higher and long documents lower; your business rules ride on top.
• Keep the index a derived copy. Write to the database, stream the changes across, and rebuild from scratch whenever you need to.

Example

import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.TreeMap;
import java.util.TreeSet;

public class Main {

    static final Set<String> STOP = Set.of("the", "a", "of", "and");

    // The analyzer. The same one must run over documents AND over queries.
    static List<String> analyze(String text) {
        List<String> out = new ArrayList<>();
        for (String token : text.toLowerCase().split("[^a-z0-9]+")) {
            if (!token.isEmpty() && !STOP.contains(token)) out.add(token);
        }
        return out;
    }

    // AND query: intersect the postings lists of every term.
    static Set<Integer> search(Map<String, TreeSet<Integer>> index, String query) {
        List<String> terms = analyze(query);
        if (terms.isEmpty()) return Set.of();
        Set<Integer> hits = new TreeSet<>(index.getOrDefault(terms.get(0), new TreeSet<>()));
        for (String t : terms.subList(1, terms.size())) {
            hits.retainAll(index.getOrDefault(t, new TreeSet<>()));
        }
        return hits;
    }

    public static void main(String[] args) {
        Map<Integer, String> docs = new LinkedHashMap<>();
        docs.put(7, "Blue Suede Shoes");
        docs.put(19, "The running shoes, and a blue bag");
        docs.put(31, "Leather boots of the north");

        // The inverted index: term -> the documents that contain it.
        Map<String, TreeSet<Integer>> index = new TreeMap<>();
        docs.forEach((id, text) -> analyze(text).forEach(term ->
                index.computeIfAbsent(term, k -> new TreeSet<>()).add(id)));

        System.out.println("postings list:");
        index.forEach((term, ids) -> System.out.println("   " + term + " -> " + ids));

        System.out.println("search(BLUE Suede) -> " + search(index, "BLUE Suede"));
        System.out.println("search(the shoes) -> " + search(index, "the shoes"));
        System.out.println("search(sandals)   -> " + search(index, "sandals"));
    }
}

Search is an analyzer plus postings lists plus a ranking function - and the index is always a derived copy.