StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>You will get much better performance if you don't do <code>LIKE '%<text>%'</code> because this will not use the index correctly, instead you should use <code>LIKE '<text>%'</code>. I would suggest you consider if you want users to be able to search for <code>name_searchable LIKE '%s%'</code> and the associated performance hit when the query takes a very long time and returns far too many results.</p> <p>Have you tried</p> <pre><code>SELECT CASE WHEN name = 'björn borg' THEN 1 WHEN name_searchable = 'bjorn borg' THEN 2 WHEN name LIKE '%björn borg%' THEN 3 WHEN name_searchable LIKE '%bjorn borg%' THEN 4 ELSE 5 END AS rank, * FROM myUsers WHERE name = 'björn borg' OR name_searchable = 'bjorn borg' OR name LIKE '%björn borg%' OR name_searchable LIKE '%bjorn borg%' ORDER BY CASE WHEN name = 'björn borg' THEN 1 WHEN name_searchable = 'bjorn borg' THEN 2 WHEN name LIKE '%björn borg%' THEN 3 WHEN name_searchable LIKE '%bjorn borg%' THEN 4 ELSE 5 END </code></pre> <p>Of course the fastest way to do this would be to add a <code>LIMIT 1</code></p> <p>Another option is to only use the like searches when the exact matches fail:</p> <pre><code>SELECT CASE WHEN name = 'björn borg' THEN 1 WHEN name_searchable = 'bjorn borg' THEN 2 WHEN name LIKE '%björn borg%' THEN 3 WHEN name_searchable LIKE '%bjorn borg%' THEN 4 ELSE 5 END AS rank, * FROM myUsers WHERE name = 'björn borg' OR name_searchable = 'bjorn borg' OR ( NOT EXISTS (SELECT TOP 1 1 FROM myUsers WHERE name = 'björn borg' OR name_searchable = 'bjorn borg' ) AND ( OR name LIKE '%björn borg%' OR name_searchable LIKE '%bjorn borg%' ) ) ORDER BY CASE WHEN name = 'björn borg' THEN 1 WHEN name_searchable = 'bjorn borg' THEN 2 WHEN name LIKE '%björn borg%' THEN 3 WHEN name_searchable LIKE '%bjorn borg%' THEN 4 ELSE 5 END </code></pre>

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