Can Pre-Appended Wildcards be Used in MySQL Fulltext Search?

  • Thread starter Thread starter dduardo
  • Start date Start date
  • Tags Tags
    Mysql
Click For Summary
SUMMARY

Pre-appended wildcards are not supported in MySQL fulltext search, which only allows appended wildcards. Users can utilize the LIKE operator with wildcards on both ends of search terms, but this lacks the advanced capabilities of boolean operators and relevance sorting. A recommended alternative is to develop a custom search engine for improved performance, as fulltext search in MySQL is often slow. Efficient indexing algorithms are crucial for optimizing search operations.

PREREQUISITES
  • Understanding of MySQL 8.0 fulltext search capabilities
  • Familiarity with SQL query syntax and boolean operators
  • Knowledge of indexing algorithms for database optimization
  • Experience with custom search engine development
NEXT STEPS
  • Research MySQL 8.0 fulltext search limitations and alternatives
  • Learn about SQL LIKE operator and its performance implications
  • Explore efficient indexing algorithms such as B-trees and hash indexing
  • Investigate frameworks for building custom search engines
USEFUL FOR

Backend developers, database administrators, and anyone looking to enhance MySQL search performance and implement advanced querying techniques.

dduardo
Staff Emeritus
Science Advisor
Insights Author
Messages
1,902
Reaction score
3
Do you guys know if it is possible to use pre-appended wildcards when doing a fulltext search? Is there a workaround that I could possibly use? I've read through the documentation and it seems as if you can only do appended wildcards. Here is my query:

Code: 
$query = "SELECT title, MATCH (title) AGAINST ('" . $search . "' IN BOOLEAN MODE) AS relevance FROM descriptions WHERE MATCH (title)  AGAINST ('" . $search . "*' IN BOOLEAN MODE) ORDER BY relevance DESC"

I know I can use LIKE with % on both ends of each word, but I need the ability to use boolean operators for advanced searching and have my query sorted by relevance.
 
Last edited:
Computer science news on Phys.org
Ok, I just talked to some guys over at the mysql chatroom on freenode. They recommended writing my own search engine since fulltext is slow.

If anyone has worked with databases before can you recommend an efficient indexing algorithms.
 
Last edited:

Similar threads

Searching for unusual characters in a Word document
  • · Replies 2 ·
Replies
2
Views
2K
Wildcard google-search to find email addresses of company?
  • · Replies 18 ·
Replies
18
Views
6K
Python Query runs fine in MySQL, but returns empty list in Python
  • · Replies 7 ·
Replies
7
Views
4K
Calculators Software to do latent semantic analysis
  • · Replies 1 ·
Replies
1
Views
2K
Autonomous research with large language models
  • · Replies 3 ·
Replies
3
Views
3K
Other Is there a good tool to get titles of lists of references?
  • · Replies 2 ·
Replies
2
Views
1K
Undergrad 7Li(p,e+e−)8Be direct proton-capture reaction X17
  • · Replies 7 ·
Replies
7
Views
3K
Undergrad What Key Issues Should Cosmologists Address in the ΛCDM Model?
  • · Replies 1 ·
Replies
1
Views
3K
Undergrad Can You Use the Appended Matrix Method for 4x4 Determinants?
  • · Replies 1 ·
Replies
1
Views
1K
Java Searching & Displaying Items in Android App Using Java & SQL
  • · Replies 5 ·
Replies
5
Views
3K