StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POmysql search where clause with each condition having different score
text
Body
copied!<p><strong>In Sum</strong></p> <p>I have two columns, name and name_searchable. I'd like to search in these columns (which have millions of rows) and return the results in accordance with their match rate. I have two important criteria; the search should be efficient and fast. How can I achieve this?</p> <p><strong>In Details</strong></p> <p>I am planning to have a table with millions of rows. So basically, I created a dump table just to test the query with a million rows. The table is using the <code>MyISAM</code> storage engine, it's index and primary key is the id number. The search I'd like to make is concerned with the name field which is a <code>varchar</code> column. Now, based on a query, I'd like to return all results that match partly or in whole with the query. So when a user searches for 'björn borg' I'd like to return both:</p> <ul> <li>björn borg</li> <li>björn borgus</li> <li>bjorn borg (notice the o)</li> </ul> <p>and so on...</p> <p>The important factor here is that the <code>=</code> operator should always return higher ranked than the <code>LIKE</code> operator. Therefore, 'björn borg' should always come before 'bjorn borgus'.</p> <p>Lately, I asked the question on how to return results in diacritics insensitive mode, but unfortunately I could not make it work. Therefore, I've created another column along the name column which stores the name in only English characters. So we have the <code>name</code> and <code>name_searchable</code> field. </p> <p>Well, I tried the whole thing with a stored procedure, but apparently it's really slow when compared to normal queries. Therefore, I'd like know whether I can order the results in accordance with which where clause they match. In other words:</p> <pre><code>SELECT * FROM myUsers WHERE name = 'björn borg' OR name_searchable = 'bjorn borg' OR name LIKE '%björn borg%' OR name_searchable LIKE '%bjorn borg%'; </code></pre> <p>So basically, the idea is to give a score point to each condition differently. I mean, while name = 'björn borg' should have the rank, say, 5, name_searchable LIKE '%bjorn borg%' should have 2 (and the second one 4 points, the third one 3 points...) How can I make this work using MySql? (Efficiency and Speed are important factors to me)</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload