For fast, relevance-ranked searches on Ubuntu, replace leading-wildcard LIKE '%term%' queries with a MySQL FULLTEXT index and MATCH() ... AGAINST(). Use natural-language mode when you want MySQL to rank results by term significance; use boolean mode when users must require, exclude, quote, or prefix-match terms. If two-letter codes are missing, change InnoDB’s minimum token size before rebuilding the affected indexes. For Chinese, Japanese, or Korean text without spaces, create the index with MySQL’s ngram parser instead of changing ordinary word-length settings.
Need a server to try this on? Antihost offers Linux VPS plans with NVMe storage, full root access and a 14-day free trial.
What MySQL full-text search changes
A conventional query such as WHERE body LIKE '%ubuntu%' checks text for a substring. It does not calculate which matching document is more relevant, and a leading wildcard generally prevents a normal B-tree index from narrowing the scan efficiently. MySQL full-text search builds an inverted index for the words in selected columns. MATCH(title, body) AGAINST (...) then searches that indexed collection.
As an Amazon Associate I earn from qualifying purchases.
The Tool Desk
Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →A MySQL text collection is the set of columns covered by one FULLTEXT index. The columns named in MATCH() must correspond to that indexed collection. Natural-language mode gives relatively rare terms more weight and common terms less weight, returning a relevance value that you can sort on. This is why full-text search is useful for article search, documentation, product descriptions, and other text where the order of results matters.
Full-text search is not a universal substring engine. Tokenization, minimum word length, stopwords, parser choice, and search mode all affect what can be found. A search for a word that was never indexed cannot return it until the index configuration is corrected and the index is rebuilt.
#1 Best Overall
- [Ultra-Lightweight Ear Buds Designed for Small Ears] Each earbud weighs only 3.7g and features a compact, ergonomic in-ear design made especially for small ears. Secure, low-profile, and comfortable for workouts, all-day wear or overnight sleeping.The ultra-slim profile sits flush in your ear with no hard edges poking or rubbing, so you can even sleep on your side without discomfort.
- [Immersive Stereo Sound with TOZO OrigX Technology] TOZO OrigX tuning delivers clear vocals, balanced mids, and natural stereo sound for music, podcasts, and videos.
- [Long Battery Life for Daily Use] Get up to 7 hours of playtime on a single charge, with up to 32 hours total using the charging case—ideal for workdays, commuting, and extended listening sessions.
- [Bluetooth 5.3 & Stable Connection] Bluetooth 5.3 provides fast pairing, stable wireless performance, and reduced dropouts as you move around home or office.
- [Deep Bass with Clear Vocals] High-performance drivers produce punchy bass while keeping vocals clean and detailed for everyday listening.
Install and verify MySQL on Ubuntu
On an Ubuntu system using the distribution packages, install the server from a terminal:
sudo apt install mysql-serverCanonical’s Ubuntu Server documentation gives that command as the standard installation method. Verify that the daemon is running with either service-management command:
sudo service mysql status
systemctl status mysqlThe output should show an active MySQL service. If it is stopped, start it with sudo systemctl start mysql, then check the status again. Confirm the client is available with mysql --version. Ubuntu packages keep MySQL configuration under /etc/mysql; do not assume that every release uses the same included filename.
Before changing search settings, inspect the options MySQL is actually loading. Running mysqld --verbose --help displays the default option-file locations and effective defaults. Put site-specific settings in the package’s included server configuration file under /etc/mysql, rather than editing a generated file that a package upgrade could replace.
Create an InnoDB table and FULLTEXT index
For a typical article search, index every column that users are allowed to search together. This example creates an InnoDB table with a combined title-and-body collection:
CREATE TABLE articles (
id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(200) NOT NULL,
body TEXT NOT NULL,
FULLTEXT KEY ft_articles_title_body (title, body)
) ENGINE=InnoDB;The index name is ft_articles_title_body. Later, the same column list must be used in MATCH(title, body). If you want separate relevance behavior for title and body, create separate collections and query them separately; do not silently use a different column list from the one defined by the index.
Outdated Drivers Are Slowing You Down
One free scan finds every outdated or missing driver and matches the right update for your exact hardware.Free scan · exact hardware matchPC Slower Than It Used to Be?
A free scan shows the junk files, broken settings and background clutter dragging Windows down - then fixes them in one click.Free scan · Windows 10 & 11Adding a full-text index to an existing table uses the same syntax:
Rank #2
- 【IP68 Waterproof Multifunctinal Smart Watch】Torjalph fitness activity tracker smart watch is IP68 waterproof technology ,you can wear it when swimming,handwashing,it's rain-proof which means that you perform multiple exercise modes outside without worrying about sweat or rain corroding your fitness smart watch,and this watch equipped with a 230 mAh large capacity battery magnetic charging ,and this sports activity tracker watch can be used 5-7 days If fully charged.
- 【Larger 1.83'' Full Screen Watch & Multi-dial Selection】1.83 inch full HD color touch screen smart watch for men / women provides excellent 240*284 resolution image quality ,This Bluetooth smart watch offers a better touch experience and remarkable immersion and ,more than 200 watch faces for you to choose from App"Da Fit ", you can change the dial style according to your mood to wear keeping the world on your wrist
- 【Accurate Activity Tracker Smart Watch 】sports watch activity tracker uses sweat and dust resistant breathable silicone strap, our Fitness Tracker Smart Watch for Android iOS Phones supports 12 sports modes,walking,running,bicycle,swimming,badminton,basketball,football,rope skipping. This bluetooth calls smart watch for men women is lightweight and stylish, bringing more convenient and relaxing sports experience while also providing more protection.
- 【Your 24 Hour Personal Daily Health Monitor】Fitness watch for women men full touch screen IP67 waterproof bluetooth smartwatch for Android iOS Phones with heart rate blood pressure monitor multifunction Andriod smart watch with steps counter sleep monitor sports fitness watch, monitoring blood oxygen saturation allows you to understand your physical condition in real time to track fitness progress ,to know your health status in time.
- 【More Functions Bluetooth Call Smart Watch】This fitness activity smart watch full screen for iPhone and Android phones includes a bunch of new features including:The bluetooth smart watch can hang up the phone, receive and display WhatsAPP /Text /Facebook /Twitter /Messenger reminder.Support sedentary reminder, alarm clock, stopwatch, timer,weather information,music control, remote camera control compatible with Android 5.0 and above or iOS 9.0 and above smartphones.
ALTER TABLE articles
ADD FULLTEXT KEY ft_articles_title_body (title, body);Build the index before deploying queries that depend on it. InnoDB is the usual choice for new Ubuntu applications because it supports transactional workloads. MyISAM has different full-text defaults and limitations, so its behavior should be treated separately rather than assumed to match InnoDB.
Use natural-language mode for ranked results
Natural-language mode is the default when no mode modifier is supplied, and it can be stated explicitly. A parameterized query that returns a score and sorts by it looks like this:
SELECT id, title,
MATCH(title, body) AGAINST (? IN NATURAL LANGUAGE MODE) AS score
FROM articles
WHERE MATCH(title, body) AGAINST (? IN NATURAL LANGUAGE MODE) > 0
ORDER BY score DESC;Bind the same user search string to both placeholders. The first occurrence produces the score shown to the application; the second filters out rows with no positive relevance. Sorting in descending order puts the highest-scoring matches first.
Quick wins for a faster PC:
Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Clear out junk files and repair common Windows errorsFree Scan →Scan for outdated or missing drivers - takes under a minuteDriver Scan →How ranking is determined
Natural-language relevance is based on the terms in the indexed collection. A term that appears in relatively few documents contributes more than a term that appears in many documents. Consequently, a distinctive phrase can rank above a result containing only common words, even when both rows technically match.
The score is useful for ordering and for deciding whether to display a result, but it is not a universal percentage or a promise that the first row is a human-perfect answer. Keep the query and the indexed column set consistent, and test ranking with the vocabulary your users actually enter.
When natural language returns nothing
A zero result can mean that every query term was removed as too short or as a stopword, not that the table is empty. It can also mean the term was added after an index was built with different settings, or that the query’s columns do not match the full-text collection. The diagnostic steps later in this guide distinguish those cases.
Use boolean mode for explicit control
Boolean mode lets the query express requirements and exclusions instead of relying only on relevance scoring:
Do these 3 things before closing this tab:
1Clear out junk files and repair common Windows errors2Scan for outdated or missing drivers - takes under a minute3Repair Windows errors before they cause bigger problemsRank #3
- [Low Noise High Fidelity] New bee guitar cord with 22AWG oxygen-free-copper center conductors provides enhanced signal transfer, the PVC insulation material and the high-density stranded copper double shield provide noise rejection for quiet operation
- [Ideal for Choice] New bee 1/4 straight and right angle metal connectors, ensures a secure connection in any instrument, pedal, or amp jack. L shape great fro on stage guitar stands
- [Wide Compatible] Suitable for mixer, power amplifier, microphone, effector, decoder, equalizer, electoronic organ, electric guitar, electric bass and other electronic equipments. Widely used in the installation of smart home, karaoke speaker, professional stage audio, etc
- [Easy Disassemble] Making incidental repairs quick and easy! Very nice, flexible cable
- [Dont Hesite to Order] 24 hours customer services and professional technology team are standing by
SELECT id, title
FROM articles
WHERE MATCH(title, body)
AGAINST ('+ubuntu +mysql -windows "full text"*' IN BOOLEAN MODE);In this example, +ubuntu and +mysql are required, -windows excludes rows containing that term, and the quoted words specify a phrase. The trailing * keeps a prefix from being stripped, allowing words that begin with the quoted prefix to match according to MySQL’s boolean parser rules.
| Syntax | Meaning | Typical use |
|---|---|---|
+term |
Term is required. | Search forms where every selected concept must appear. |
-term |
Term is excluded. | Removing an unwanted topic from broad results. |
"a phrase" |
Words are searched as a phrase. | Exact names, product models, or quoted wording. |
prefix* |
Retains a trailing prefix wildcard. | Autocomplete-like matching of words that share a beginning. |
Boolean mode is also important for MyISAM collections when common terms are involved. MyISAM natural-language search applies a 50-percent threshold: a word appearing in at least half of the rows can be treated as too common to contribute to a natural-language result. MySQL documents that boolean mode does not observe that MyISAM threshold. This exception does not remove InnoDB’s token-length or stopword rules; those still determine what was indexed.
Boolean mode does not automatically provide the same relevance-ranking workflow as the natural-language query above. Choose it when the operators themselves are the product requirement. If you need both mandatory terms and an ordered relevance score, design and test that behavior explicitly rather than assuming a boolean result set is ranked identically to natural language.
Why MATCH … AGAINST can miss a term
InnoDB’s minimum token length
For InnoDB full-text indexes, the default minimum indexed word length is three characters. A two-character code such as AI, R2, or a regional abbreviation is therefore omitted by default. A query for that code cannot find rows through the index because the token is absent.
MyISAM has a different default: the minimum word length is four characters. Do not diagnose a MyISAM table with InnoDB’s three-character assumption.
Stopwords
MySQL omits stopwords, which are words considered too common to be useful for full-text matching. InnoDB stopword behavior is controlled by innodb_ft_enable_stopword, innodb_ft_server_stopword_table, and innodb_ft_user_stopword_table. MyISAM uses ft_stopword_file. A term can therefore disappear even when it is long enough, simply because the active stopword configuration excludes it.
Parser and token boundaries
The built-in parser uses delimiters such as whitespace to identify words. Punctuation and languages that do not put spaces between words can produce tokens different from what a user sees on screen. If the content is Chinese, Japanese, or Korean and words are not separated by spaces, use the ngram parser described below.
Rank #4
- Digital Family Calendar: BSIMB digital calendar wall touch screen puts an end to your chaotic life. Designed for busy families, the digital calendar keeps you organised and in step with your family. Assign different colours and tasks to each family member to make family scheduling easier and more intuitive.
- Easy Setup and Auto-Sync: Plug in, connect to WLAN and auto-syn your electric calendar in minutes. Digital wall calendar compatible with Google calendar, iCloud calendar, Outlook calendar, Cozi calendar and Yahoo calendar. Add events directly on the device or through the ecalendar app. Electronic calendar stays current whether you're at work or traveling—no manual updates needed.
- All-In-One Home Assistant: Smart calendar builds habits and encourage independence with personalised chores. Display daily meal plan to reduce the daily ‘What's for dinner?’ stress. To-do lists keep track of everything you want. Turning chores into valuable achievements with stars and rewards, making life easier and more enjoyable.
- Manage Anytime Anywhere: BSIMB electronic calendar for family stay connected with the powerful ecalendar app. Access and manage your digital wall calendar anytime, anywhere with free features like stars & rewards, photo screensaver, chore charts, meals plans, to-do lists etc. Electronic planner paid features like Magic Import tailor advanced tools for users with special needs.
- Stay in Touch with Loved Ones: Share photos or videos from your phone to electronic wall calendar with app, instantly update your life moment, and keep in touch with your family, especially those who are far away; you can also surprise your smart calendar digital wall by sending him a wish card; it's also great for keeping tech-illiterate elders in touch with the youngsters.
Index and query mismatch
Check that every column in MATCH() is covered by the same full-text index and that the query uses the intended table. A newly changed token-size or stopword setting does not retroactively alter an existing index; the old tokenization remains until that index is rebuilt.
Make two-character terms searchable safely
Changing the minimum token size is a schema-and-operations migration, not just a query tweak. To index two-character InnoDB terms, add this setting to the active MySQL server configuration:
[mysqld]
innodb_ft_min_token_size=2Use the included server configuration file under /etc/mysql, after confirming the option-file order with mysqld --verbose --help. Record the old value and apply the new value consistently on every server that builds or serves the index. If the option requires a restart in your package version, restart during a maintenance window:
sudo systemctl restart mysql
systemctl status mysqlChanging the variable alone is insufficient. MySQL’s tuning documentation requires rebuilding every affected FULLTEXT index. For the example table:
ALTER TABLE articles
DROP INDEX ft_articles_title_body,
ADD FULLTEXT KEY ft_articles_title_body (title, body);Plan for the rebuild’s disk, CPU, and locking impact on the table size and workload. On a replicated or multi-server deployment, make sure the setting and rebuilt index are consistent wherever searches run; otherwise identical queries can return different results.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
- Record the current minimum-token and stopword settings.
- Identify every InnoDB full-text index affected by the change.
- Set
innodb_ft_min_token_size=2in the active[mysqld]configuration. - Restart MySQL if required, and verify the service is healthy.
- Drop and recreate each affected full-text index.
- Run representative searches for two-character hits and for ordinary words that should remain excluded.
- Compare expected hits and false positives before returning the application to normal traffic.
Control stopwords as a planned migration
If your domain relies on words that MySQL treats as stopwords, review the active InnoDB stopword settings rather than changing application queries repeatedly. Decide whether the server should use its built-in list, a server stopword table, or a user stopword table, and configure the corresponding variables consistently.
Any stopword change requires the same discipline as a token-size change: existing indexes retain the old term set, so rebuild all affected indexes after the configuration change. Keep a record of the old and new lists, test common queries for both desired matches and unwanted noise, and roll out the change to every index-building server. A broader vocabulary usually increases index size and can make common-word searches less selective, so measure the operational effect on your workload.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Choose the right parser for the language
The default built-in parser works well for languages whose words are separated by delimiters. It is not designed to infer word boundaries in CJK text that has no spaces. For Chinese, Japanese, and Korean content, create the index with MySQL’s ngram parser:
CREATE FULLTEXT INDEX ft_cjk
ON documents (content) WITH PARSER ngram;The ngram parser divides text into fixed-length character sequences. Its default ngram_token_size is 2 (a bigram), and MySQL allows a configurable range from 1 through 10. Tune ngram_token_size for the language and search behavior you need. The ngram parser ignores the ordinary minimum-word-length variables, so changing innodb_ft_min_token_size will not alter an ngram index.
| Characteristic | Built-in parser | ngram parser |
|---|---|---|
| Segmentation | Uses delimiters such as whitespace to identify words. | Splits text into fixed-length character ngrams. |
| Best fit | Space-delimited languages and conventional word search. | Chinese, Japanese, Korean, and other text without reliable spaces. |
| Length setting | InnoDB minimum token size applies; default is three characters. | ngram_token_size applies; default is 2 and the documented range is 1–10. |
| Index implications | Indexes words as parsed by delimiters. | Can create more overlapping tokens, increasing index volume as token size and text require. |
| Phrase and wildcard behavior | Follows ordinary word-token rules and boolean operators. | Queries are interpreted through ngram tokens, so test phrase and prefix expectations with representative language samples. |
InnoDB and MyISAM full-text behavior
| Area | InnoDB | MyISAM |
|---|---|---|
| Default minimum word length | Three characters. | Four characters. |
| Stopword controls | innodb_ft_enable_stopword, innodb_ft_server_stopword_table, and innodb_ft_user_stopword_table. |
ft_stopword_file. |
| Common-term threshold | Do not apply the MyISAM 50-percent rule to InnoDB. | Natural-language mode can suppress terms present in at least half of the rows; boolean mode bypasses that threshold. |
| Engine choice | Appropriate for transactional application tables and current Ubuntu deployments. | Retained in some legacy schemas; verify its distinct full-text defaults before changing queries. |
The engine matters when you troubleshoot. A query copied from a MyISAM example may fail on InnoDB because the minimum token length or stopword configuration differs, while an apparently ordinary MyISAM term may be ignored by natural-language ranking because it is too common.
A practical search implementation workflow
- Define the searchable fields. Decide whether title, body, tags, or another text field belongs in one collection. Keep the decision aligned with the columns named in
MATCH(). - Create or verify the index. Use an InnoDB
FULLTEXTkey and confirm its column order and name in the table definition. - Start with natural language. Return a score, filter scores above zero, and sort descending when relevance ranking is the desired user experience.
- Add boolean mode for advanced controls. Parse or constrain user input so required, excluded, phrase, and prefix operators are intentional rather than accidental.
- Test short and common terms. Include two-character codes, stopwords, punctuation, and words that occur in many rows. Record which misses are expected from the active configuration.
- Choose a parser by language. Use the built-in parser for delimiter-separated words; use
WITH PARSER ngramfor unspaced CJK content. - Version configuration with the schema. Store token-size, stopword, and parser decisions alongside migration scripts so a new server builds the same index.
- Rebuild deliberately after changes. Changing a full-text option without rebuilding leaves the old index behavior in place.
Troubleshoot common symptoms
| Symptom | Likely cause | Action |
|---|---|---|
| A two-letter code returns no rows. | InnoDB’s default minimum token size is three, or the term is a stopword. | Review token and stopword settings, set innodb_ft_min_token_size=2 if appropriate, restart if required, and rebuild the index. |
| A common word is ignored in natural-language MyISAM search. | The MyISAM 50-percent threshold treats it as non-discriminating. | Try boolean mode when that term must be included, and verify the term is not also a stopword. |
| Changing configuration did not change results. | The existing index was not rebuilt. | Drop and recreate every affected FULLTEXT index, then retest. |
| CJK words do not match as users expect. | The built-in parser cannot infer unspaced word boundaries. | Use an ngram full-text index and tune ngram_token_size. |
| MySQL reports a full-text query or index error. | The MATCH() column list does not correspond to the indexed collection, or the table lacks the required full-text index. |
Compare the CREATE TABLE/CREATE INDEX definition with the query and use the same indexed columns. |
| Results appear but the order is poor. | The mode, indexed fields, or vocabulary does not match the ranking goal. | Use natural-language scoring for relevance, inspect which terms are rare or common, and reserve boolean mode for explicit constraints. |
Performance and maintenance considerations
A full-text index consumes storage and must be maintained as rows are inserted, updated, and deleted. Indexing additional columns broadens the searchable collection but also increases the amount of text MySQL parses. Include only fields users genuinely search.
Rebuilding after a token-size, stopword, or parser change can be an expensive table operation. Schedule it for a maintenance window, monitor disk space, and test on a representative copy before production. Keep configuration identical across primary, replica, staging, and failover servers that answer search requests.
Do not use a full-text index as a drop-in replacement for every pattern search. If the requirement is an arbitrary substring inside a token, full-text tokenization may not match it; retain a carefully scoped LIKE query or another search design for that separate use case. For ordinary word search with relevance ranking, however, FULLTEXT plus MATCH() ... AGAINST() is the MySQL-native approach.
Free tools Windows power users keep installed
One-click scans. No signup required.
Quick Recap
Recommended decision framework
- Choose InnoDB with the built-in parser and natural-language mode for a conventional, space-delimited language when ranked results are the main requirement.
- Choose boolean mode when the interface needs mandatory terms, exclusions, quoted phrases, or trailing-prefix control.
- Change InnoDB’s minimum token size only when short domain terms are genuinely searchable requirements; rebuild indexes and test false positives afterward.
- Review stopword configuration when important vocabulary is silently omitted, and rebuild after any list or enablement change.
- Choose ngram for unspaced CJK text and tune
ngram_token_size; ordinary InnoDB minimum-token settings do not control ngram indexes. - Handle MyISAM as a separate compatibility case because its default token length, stopword variable, and 50-percent common-term rule differ from InnoDB.
Final checklist
- MySQL is installed with APT and the service is active.
- The table has a
FULLTEXTindex covering the exact columns queried. - Natural-language searches return a score and sort by it when ranking is required.
- Boolean operators are used deliberately for required, excluded, phrase, and prefix terms.
- Two-character and stopword behavior has been tested against the active configuration.
- Every token-size or stopword change is followed by a full-text index rebuild.
- CJK content uses the ngram parser with an appropriate token size.
- Configuration and rebuilt indexes are consistent on every server that serves searches.
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
