How does MySQL handle full-text indexing vs regular B-tree indexing for large text searches?

For large text search operations, B-tree indexes and FULLTEXT indexes serve fundamentally different purposes and have different performance characteristics. B-tree index + LIKE: WHERE description LIKE "mysql%" — only efficient with a trailing wildcard (can use index prefix scan). A leading wildcard (LIKE "%mysql" or LIKE "%mysql%") triggers a full table scan — O(n) regardless of index. B-tree is appropriate for exact matches or prefix searches on short strings (first name, SKU codes). FULLTEXT index + MATCH...AGAINST: uses an inverted index — maps every word to the list of documents containing it. Supports natural language search with relevance ranking, phrase search, Boolean operators. Finding documents containing "mysql optimization" is O(log n) using the inverted index. FULLTEXT indexes are maintained synchronously on write. Limitations: minimum word length filters; stop words ignored; InnoDB FULLTEXT requires table-level lock during index build on large tables. Performance at scale: MySQL FULLTEXT is suitable for moderate sizes (millions of records). For billions of records, complex relevance, fuzzy matching, faceted search, and geo-search, use Elasticsearch or MeiliSearch, fed via binlog CDC or application writes, with MySQL as the source of truth.