0

I'd like to improve my MySQL search functionality. When searching "Fi" in my car database, I would like FIAT to be ranked first, as I feel a cars brand should have a higher rank than the model version. I want to tell MySQL if the search term matches the first letters of a Brand then prioritise that result first.

{My Search results]1

Here's my current code

$sql = "SELECT v.id, v.model, v.model_version, v.model_year, b.brand FROM vehicles v LEFT JOIN brands b ON v.brand_id = b.id WHERE CONCAT(b.brand, ' ', v.model, ' ', v.model_version) LIKE ? LIMIT 6";
nbk
  • 45,398
  • 8
  • 30
  • 47
Nitx96
  • 5
  • 3
  • 2
    This has no relation to JS, autocomplete, and little/none to PHP. You should look at full text indexing. https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html. – user3783243 Dec 30 '20 at 01:21
  • Right! This seems to make sense. Tried this, but it doesnt work: $sql = "SELECT v.id, v.model, v.model_version, v.model_year, b.brand FROM vehicles v LEFT JOIN brands b ON v.brand_id = b.id WHERE MATCH(b.brand, v.model, v.model_version) AGAINST (? IN NATURAL LANGUAGE MODE)"; – Nitx96 Dec 30 '20 at 07:44
  • Did you create the index already? Can you define `doesnt work` a bit more? Doesn't give results, doesn't rank as expected, etc? – user3783243 Dec 30 '20 at 11:23
  • Index is created for brands, model and model_version. Error I am getting is "#1210 - Incorrect arguments to MATCH" As soon as I remove Brands, I get a result. So somehow it has issue with me adding brands to the mix. – Nitx96 Dec 31 '20 at 01:22
  • Can you add result of `show create table brands`? – user3783243 Dec 31 '20 at 02:15
  • Hmm I'm not sure I understand what you mean? You want me to run that SQL alone? – Nitx96 Dec 31 '20 at 02:18
  • Yes, that will show how the table is built and the index, and likely cause of 1210 error. – user3783243 Dec 31 '20 at 03:24

2 Answers2

0

The comment by @user3783243 is the right answer. If you want to do it with mysql, define a rank column along these lines: 4 * if(b.brand like ?, 1, 0) + 2 * if(v.model like ?, 1, 0) + if(v.model_version like ?, 1, 0) as 'rank'. It gets tricky quickly. Let's say there is exact mach on the model but a partial match on the brand. Should the partial brand really be ranked higher?

Allan Wind
  • 23,068
  • 5
  • 28
  • 38
  • Thanks for this, it works. But you're right, it gets complicated quickly. Makes you quickly appreciate the complexity of googles search engine ha... – Nitx96 Dec 30 '20 at 07:45
  • Please mark it solved if that answered your question. – Allan Wind Dec 30 '20 at 20:41
0

I found the best answer that works really well I believe. As full-text searches only index up to 4 characters (unless you change server settings) this can be a hassle.

Here was my approach:

SELECT v.id, v.model, v.model_version, v.model_year, b.brand FROM vehicles v
          LEFT JOIN brands b ON v.brand_id = b.id
          WHERE (
            CONCAT(b.brand, ' ', v.model, ' ', v.model_version) LIKE 'fi%' OR
            CONCAT(b.brand, ' ', v.model, ' ', v.model_version) LIKE '%fi%' OR
            CONCAT(b.brand, ' ', v.model, ' ', v.model_version) LIKE '%fi'
          ) ORDER BY case
              WHEN CONCAT(b.brand, ' ', v.model, ' ', v.model_version) LIKE 'fi%' THEN 1
              WHEN CONCAT(b.brand, ' ', v.model, ' ', v.model_version) LIKE '%fi%' THEN 2
              WHEN CONCAT(b.brand, ' ', v.model, ' ', v.model_version) LIKE '%fi' THEN 3
            ELSE 4 END
          LIMIT 6
Nitx96
  • 5
  • 3
  • 1
    In the `where` clause you only need the `'%fi%'` case. If you don't need your search to cover overlapping fields, you might get better performance if your `where` clause look at individual fields opposed to constructed one. If you need the constructed column is a requirement, your query would cleaner with a common table expression. I don't know if that has any performance impact. `with select v.id, CONCAT(b.brand, ' ', v.model, ' ', v.model_version) brand_model_version select ... where brand_model_version like ...` – Allan Wind Dec 31 '20 at 04:13
  • The above should have been `with t select v.id ... select id, brand_model_version from t where brand_model_version lile '%fi%' order by ...` – Allan Wind Dec 31 '20 at 04:20