Member Avatar for tulipputih

Hello,
Is there any way to list the search result based on the relevance of that record vs keywords, not depending on the sequence of that record in database?

Thank you

  • 2 Contributors
  • 3 Replies
  • 102 Views
  • 6 Hours Discussion Span
  • Latest Post Latest Post by tulipputih

Dani AI

Generated

As noted, ordering by relevance is the usual way to return useful search results. Column weighting is an easy, effective technique: compute a relevance score per row by running a search against each column, multiply each column score by a weight, sum the results, and ORDER BY that sum. The example below uses MySQL FULLTEXT (fast and built for relevance scoring) and a simple PDO call in PHP.

Create fulltext indexes (InnoDB supports FULLTEXT since MySQL 5.6) and run a weighted query:

ALTER TABLE articles ADD FULLTEXT (title);
ALTER TABLE articles ADD FULLTEXT (body);

SELECT id, title,
  (MATCH(title) AGAINST(:terms IN NATURAL LANGUAGE MODE) * 3.0
   + MATCH(body)  AGAINST(:terms IN NATURAL LANGUAGE MODE) * 1.0) AS relevance
FROM articles
WHERE MATCH(title, body) AGAINST(:terms IN NATURAL LANGUAGE MODE)
ORDER BY relevance DESC
LIMIT 50;

Example PDO usage:

$stmt = $pdo->prepare(/* the SELECT above */);
$stmt->execute([':terms' => $searchTerms]);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

Troubleshooting and tuning: ensure FULLTEXT indexes exist for the MATCH columns; adjust server settings like ft_min_word_len / innodb_ft_min_token_size and stopword lists if short/common words fail to match; use BOOLEAN MODE or phrase queries to force required words or exact-phrase boosts. If precise phrase boosts are needed, add an extra MATCH(...) AGAINST('"some phrase"' IN BOOLEAN MODE) term with a high multiplier. See MySQL FULLTEXT details for behavior and tuning (MySQL FULLTEXT docs, boolean mode).

If dataset size or feature needs grow (fuzzy matching, stemming, advanced ranking), move to a dedicated search engine such as Sphinx or Elasticsearch for better scalability and relevance features (Sphinx docs).

Recommended Answers

All 3 Replies

Member Avatar for kvprajapati

Yes it is.

Member Avatar for tulipputih

All columns in a table has their weight.
thanks

Member Avatar for tulipputih

Could you please give an example?
tq.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.