StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMysql - full text search - match value in where?
text
Body
copied!<p>I have query like this</p> <pre><code>SELECT id, search, MATCH(search) AGAINST('+iron +maiden') m FROM mp3 WHERE MATCH(search) AGAINST('+iron +maiden') ORDER BY m DESC; </code></pre> <p>Works perfectly fine, results are sorted, everythings good. Then I tried to limit my results to some best matches. I noticed m is sth between 4 and 20 (not always) so i thought:</p> <pre><code>SELECT id, search, MATCH(search) AGAINST('+iron +maiden') m FROM mp3 WHERE m > 10 AND MATCH (search) AGAINST ('+iron +maiden') ORDER BY m DESC; </code></pre> <p>Bam! <em>ERROR 1054 (42S22): Unknown column 'm' in 'where clause'</em></p> <p>Now I know it's not going to work, but I don't know why, maybe I'm tired or something... So I'd like to ask you:</p> <ol> <li>Why it didn't work?</li> <li>Is there similar working solution? (sql only, I know i can filter results later)</li> </ol> <p>Cheers</p> <p><strong>EDIT</strong> So as @alko suggested I did some benchmarking and it looks promising:</p> <pre><code>Match against within inner select (without WHERE m > 10 so resultset is identical as in match against below): SELECT id, search, m FROM (SELECT id, search, MATCH(search) AGAINST('+metallica') m FROM mp3 WHERE MATCH(search) AGAINST('+metallica')) matched_mp3 ORDER BY m DESC; 2818 rows in set (0.03 sec) SELECT id, search, m FROM (SELECT id, search, MATCH(search) AGAINST('+dead +weather') m FROM mp3 WHERE MATCH(search) AGAINST('+dead +weather')) matched_mp3 ORDER BY m DESC; 6968 rows in set (0.10 sec) SELECT id, search, m FROM (SELECT id, search, MATCH(search) AGAINST('+led +zeppelin') m FROM mp3 WHERE MATCH(search) AGAINST('+led +zeppelin')) matched_mp3 ORDER BY m DESC; 1381 rows in set (0.02 sec) SELECT id, search, m FROM (SELECT id, search, MATCH(search) AGAINST('+lana +del +ray +paradise') m FROM mp3 WHERE MATCH(search) AGAINST('+lana +del +ray +paradise')) matched_mp3 ORDER BY m DESC; 7447 rows in set (0.11 sec) Match against: SELECT id, search, MATCH(search) AGAINST('+metallica') m FROM mp3 WHERE MATCH(search) AGAINST('+metallica') ORDER BY m DESC; 2818 rows in set (0.03 sec) SELECT id, search, MATCH(search) AGAINST('+dead +weather') m FROM mp3 WHERE MATCH(search) AGAINST('+dead +weather') ORDER BY m DESC; 6968 rows in set (0.10 sec) SELECT id, search, MATCH(search) AGAINST('+led +zeppelin') m FROM mp3 WHERE MATCH(search) AGAINST('+led +zeppelin') ORDER BY m DESC; 1381 rows in set (0.01 sec) SELECT id, search, MATCH(search) AGAINST('+lana +del +ray +paradise') m FROM mp3 WHERE MATCH(search) AGAINST('+lana +del +ray +paradise') ORDER BY m DESC; 7447 rows in set (0.12 sec) Match against within inner select (with WHERE m > 10) SELECT id, search, m FROM (SELECT id, search, MATCH(search) AGAINST('+metallica') m FROM mp3 WHERE MATCH(search) AGAINST('+metallica')) matched_mp3 WHERE m > 10 ORDER BY m DESC; 8 rows in set (0.02 sec) SELECT id, search, m FROM (SELECT id, search, MATCH(search) AGAINST('+dead +weather') m FROM mp3 WHERE MATCH(search) AGAINST('+dead +weather')) matched_mp3 WHERE m > 10 ORDER BY m DESC; 46 rows in set (0.08 sec) SELECT id, search, m FROM (SELECT id, search, MATCH(search) AGAINST('+led +zeppelin') m FROM mp3 WHERE MATCH(search) AGAINST('+led +zeppelin')) matched_mp3 WHERE m > 10 ORDER BY m DESC; 1070 rows in set (0.01 sec) SELECT id, search, m FROM (SELECT id, search, MATCH(search) AGAINST('+lana +del +ray +paradise') m FROM mp3 WHERE MATCH(search) AGAINST('+lana +del +ray +paradise')) matched_mp3 WHERE m > 10 ORDER BY m DESC; 532 rows in set (0.06 sec) </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