WebSockets in PHP: When Real-Time Communication Actually Makes Sense

Understanding the Full-Text Search Decision

Most web applications start with a simple requirement: let users search for content. A basic LIKE query often works fine initially. As the dataset grows and users expect faster results with better relevance ranking, the limitations of naive text matching become obvious. At that point, many developers consider moving from MySQL's built-in full-text search to a dedicated search engine like Elasticsearch. The switch is not always necessary, and it comes with real costs in terms of infrastructure, maintenance, and operational complexity.

This article explains when MySQL full-text search works adequately, when Elasticsearch becomes the practical choice, and how to evaluate whether the switch makes sense for your specific situation. The goal is to help you make an informed decision based on your actual data volumes, performance requirements, and team capacity rather than assuming that a dedicated search engine is always the better option.

How MySQL Full-Text Search Works

MySQL includes a FULLTEXT index type that enables natural language search on CHAR, VARCHAR, and TEXT columns. The index builds automatically when you add the FULLTEXT index definition to your table schema. MySQL then uses the index to rank results based on relevance, returning a relevance score that you can use for sorting.

ALTER TABLE articles ADD FULLTEXT(title, content);

SELECT title, MATCH(title, content) AGAINST('search query' IN NATURAL LANGUAGE MODE) AS relevance
FROM articles
WHERE MATCH(title, content) AGAINST('search query' IN NATURAL LANGUAGE MODE)
ORDER BY relevance DESC;

MySQL supports three matching modes: natural language mode, which ranks results by relevance; boolean mode, which supports operators like +, -, and wildcards; and query expansion mode, which broadens the search. Boolean mode is particularly useful when you need more control over what constitutes a match.

SELECT title, content FROM articles
WHERE MATCH(title, content) AGAINST('+php -javascript' IN BOOLEAN MODE);

The MyISAM storage engine originally supported full-text indexes. InnoDB added full-text support in MySQL 5.6, which is important because InnoDB is the default and recommended engine for most production workloads. If you are running MySQL 5.5 or earlier with InnoDB, you need to upgrade to use full-text search with InnoDB tables.

What Elasticsearch Brings to the Table

Elasticsearch is a distributed search and analytics engine built on top of Apache Lucene. Unlike MySQL, which stores data as structured records, Elasticsearch indexes data as documents optimised for full-text search. It handles horizontal scaling across multiple nodes, supports complex queries, and provides features that relational databases do not offer natively.

The core differences that matter most for search are:

• Relevance scoring: Elasticsearch uses the BM25 algorithm by default, which handles term frequency and document length differently from MySQL's approach. For many use cases, this produces more intuitive ranking.
• Fuzzy matching: Elasticsearch can find results that match approximately, catching typos and spelling variations without requiring exact input.
• Faceted search: You can aggregate results by category, date range, or other fields to build filtered search interfaces.
• Synonym support: Built-in synonym handling lets users search for "car" and find results containing "automobile" without changing your data.
• Highlighting: Search results can include highlighted snippets showing where the match occurs in the document.

These capabilities are not simply better versions of what MySQL does. They represent a different approach to search that becomes valuable when your requirements exceed what a relational database index is designed to handle.

When MySQL Full-Text Search Is Enough

MySQL's full-text search handles a surprising range of practical requirements. For many applications, particularly those with modest data volumes or straightforward search needs, MySQL is the simpler and more maintainable choice.

MySQL full-text search works well in these situations:

• Datasets under a few hundred thousand records: Full-text queries on well-indexed tables typically return in milliseconds for datasets in this range. If your total searchable content fits comfortably in memory, performance is rarely a concern.
• Single-language content: MySQL's built-in stemming and stopword handling works adequately for English and other major languages without additional configuration.
• Simple relevance requirements: If you need results ranked by relevance but do not require sophisticated custom scoring, MySQL's default behaviour is often sufficient.
• Transactional consistency: When search results must reflect the current state of the database immediately, MySQL avoids the synchronisation delays that come with maintaining a separate search index.
• Simple hosting environments: If you are running on shared hosting, a managed database service, or any environment where you cannot easily run separate infrastructure, MySQL keeps everything in one place.

If your application fits these parameters, adding Elasticsearch introduces unnecessary complexity. You will need to manage index synchronisation, monitor a separate service, and handle the operational overhead of keeping both systems in sync. The performance benefit, if any, will be marginal.

When Elasticsearch Becomes the Practical Choice

There are clear situations where MySQL's capabilities become a genuine constraint rather than a reasonable trade-off. Understanding these thresholds helps you plan the transition before performance problems affect your users.

Data Volume and Query Performance

When tables grow beyond a certain size, full-text search queries start competing with other database operations for resources. MySQL uses a single-threaded parser for full-text queries, which means query times increase more predictably than in a distributed search engine. On large datasets, this can mean the difference between a 20ms query and a 2-second query.

There is no universal threshold that applies to every setup. The point where performance becomes unacceptable depends on your server resources, data distribution, query patterns, and acceptable response times. A good practical approach is to test with production-equivalent data volumes and monitor query times under realistic load. If full-text queries regularly exceed your latency targets, Elasticsearch is worth evaluating.

Advanced Search Features

If your application needs fuzzy matching, complex Boolean logic, field boosting that weights matches in one column higher than another, or aggregations for faceted filtering, MySQL's full-text capabilities become limiting. Building equivalent functionality in MySQL requires workarounds that are often fragile and slow.

For example, implementing faceted search with MySQL typically involves running multiple queries and aggregating results in application code. Elasticsearch returns facets as part of a single query, which is significantly faster and produces more consistent results.

Search Quality and Relevance

MySQL's relevance scoring does not handle all search patterns intuitively. Short documents, documents with very high term frequency, and queries with many optional terms can produce unexpected rankings. Elasticsearch's BM25 algorithm addresses some of these edge cases, and its customisable scoring lets you fine-tune relevance for your specific content domain.

If users frequently report that the "wrong" results appear at the top, and simple index tuning does not resolve the issue, Elasticsearch's scoring flexibility may be necessary to deliver acceptable search quality.

High Availability and Scaling Requirements

MySQL full-text search runs on the same database instance as your transactional data. If search traffic spikes and competes with write operations, both suffer. Elasticsearch separates search from the primary database, which provides natural isolation and lets you scale search infrastructure independently.

For applications with unpredictable or highly variable search traffic, this separation is valuable. You can scale Elasticsearch horizontally without affecting your database server, and you can tune search performance separately from transactional performance.

The Operational Cost of Elasticsearch

Adding Elasticsearch is not free. Before committing to the switch, it is worth understanding what you are taking on in terms of infrastructure, maintenance, and team knowledge.

Infrastructure Requirements

Elasticsearch runs as a cluster, typically across multiple nodes for redundancy and performance. A minimal production setup needs at least three nodes to handle automatic sharding and replica distribution safely. Each node needs adequate CPU, RAM, and disk, with SSD storage strongly recommended for acceptable query performance.

This means more servers or more expensive managed services. If you are currently running on a single database server, adding Elasticsearch roughly doubles your infrastructure footprint and cost.

Index Synchronisation

Your MySQL database holds the source of truth. Elasticsearch holds a copy optimised for search. Keeping these in sync requires a synchronisation mechanism, and the choice you make affects consistency, complexity, and operational burden.

Common approaches include:

• Application-level sync: Your code writes to both MySQL and Elasticsearch after each relevant database operation. This is straightforward to implement but adds latency to writes and risks inconsistency if a write to one system fails.
• Message queue sync: Database changes trigger messages in a queue (such as RabbitMQ or Kafka), which a separate process consumes to update Elasticsearch. This decouples the systems and provides durability, but adds infrastructure and complexity.
• Log-based sync: Tools like Debezium read the MySQL binary log to capture changes and feed them to Elasticsearch. This approach minimises application changes but requires careful configuration and monitoring.

Each approach has trade-offs. Application-level sync is the easiest to implement but adds coupling between your code and Elasticsearch. Queue-based or log-based approaches are more robust but introduce additional services to monitor and maintain.

Maintenance and Monitoring

Elasticsearch clusters need ongoing attention. You need to monitor cluster health, manage shard allocation, handle node failures gracefully, and plan capacity as your data grows. Index settings need tuning for your specific use case, and you need to manage index lifecycle, including when to close old indices and how to handle reindexing when your mapping changes.

If your team does not have experience with Elasticsearch, factor in the learning curve. The official documentation is thorough, but operating a search cluster reliably requires understanding concepts like shard allocation, refresh intervals, and memory management that are distinct from relational database administration.

Making the Switch: A Practical Approach

If you have decided that Elasticsearch is necessary, approach the migration thoughtfully. Migrating search is not like switching database engines. You are adding a new system that must coexist with your existing one during a transition period.

A practical migration approach:

1. Build the Elasticsearch index from existing data: Write a script that reads from MySQL and indexes into Elasticsearch. Test the index with a subset of your data to verify mapping and scoring behave as expected.
2. Implement dual-write: Update your application to write search documents to Elasticsearch alongside MySQL writes. This keeps both systems in sync going forward.
3. Run both systems in parallel: Route a small percentage of search traffic to Elasticsearch while comparing results to MySQL. Look for discrepancies in relevance and fix any issues before increasing traffic.
4. Gradually shift traffic: As confidence builds, route more traffic to Elasticsearch. Monitor query times, error rates, and user-reported issues.
5. Remove MySQL full-text queries: Once Elasticsearch handles all search traffic reliably, remove the MySQL full-text logic from your application. Keep the FULLTEXT index in place as a fallback during the transition period.

This approach minimises risk by keeping MySQL full-text search available as a backup throughout the transition. If something goes wrong with the Elasticsearch setup, you can route traffic back to MySQL without service interruption.

Hosting and Infrastructure Considerations

Running Elasticsearch alongside MySQL has infrastructure implications that are worth considering before you commit. If your current setup is on shared hosting or a basic managed database service, you may need to move to infrastructure that supports running both systems reliably.

For applications that are outgrowing shared hosting limitations, moving to a VPS or dedicated server provides the control and resources needed for serious search workloads. This is a common step for growing applications, and it affects more than just search capabilities. You may find that other resource constraints become apparent once you have room to monitor your application properly.

If you are already on infrastructure with headroom, adding Elasticsearch is primarily a capacity planning exercise. Calculate storage requirements (Elasticsearch typically uses more disk than raw data due to its index structure), estimate memory needs based on your index size, and plan for the additional CPU overhead of search queries.

Related practical reading

These related guides can help you connect this topic with the wider website, server, security, and support decisions around it.

• GraphQL in PHP vs REST: When GraphQL Is the Better Choice - useful background for related development decisions
• PHP 8.4: Property Hooks and Asymmetric Visibility - useful background for related development decisions

Making the Right Choice for Your Situation

The MySQL versus Elasticsearch decision is not about finding the "best" technology. It is about matching your search capabilities to your actual requirements, team capacity, and infrastructure constraints. MySQL full-text search is a capable feature that handles a wide range of practical search needs. Elasticsearch is a powerful tool that becomes necessary when your requirements exceed what MySQL can deliver comfortably.

If you are currently using MySQL full-text search and it is meeting your performance and functionality needs, there is no compelling reason to change. The operational overhead of a separate search system is real, and the benefits need to justify that cost. If you are hitting the limits of what MySQL can do, planning the transition carefully helps you avoid the common pitfalls of rushed migrations.

If you want to evaluate your current search setup against your actual requirements, a practical review of your query performance, data volumes, and the search features your users depend on will clarify whether now is the right time to make the switch.