Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Create three full text indexes</p> <ul> <li>a) one on the keyword column</li> <li>b) one on the content column</li> <li>c) one on both keyword and content column</li> </ul> <p>Then, your query:</p> <pre><code>SELECT id, keyword, content, MATCH (keyword) AGAINST ('watermelon') AS rel1, MATCH (content) AGAINST ('watermelon') AS rel2 FROM table WHERE MATCH (keyword,content) AGAINST ('watermelon') ORDER BY (rel1*1.5)+(rel2) </code></pre> <p>The point is that <code>rel1</code> gives you the relevance of your query just in the <code>keyword</code> column (because you created the index only on that column). <code>rel2</code> does the same, but for the <code>content</code> column. You can now add these two relevance scores together applying any weighting you like.</p> <p>However, you aren't using either of these two indexes for the actual search. For that, you use your third index, which is on both columns.</p> <p>The index on (keyword,content) controls your recall. Aka, what is returned.</p> <p>The two separate indexes (one on keyword only, one on content only) control your relevance. And you can apply your own weighting criteria here.</p> <p>Note that you can use any number of different indexes (or, vary the indexes and weightings you use at query time based on other factors perhaps ... only search on keyword if the query contains a stop word ... decrease the weighting bias for keywords if the query contains more than 3 words ... etc).</p> <p>Each index does use up disk space, so more indexes, more disk. And in turn, higher memory footprint for mysql. Also, inserts will take longer, as you have more indexes to update.</p> <p>You should benchmark performance (being careful to turn off the mysql query cache for benchmarking else your results will be skewed) for your situation. This isn't google grade efficient, but it is pretty easy and "out of the box" and it's almost certainly a lot lot better than your use of "like" in the queries.</p> <p>I find it works really well.</p>
    singulars
    1. This table or related slice is empty.
    1. POHow can I manipulate MySQL fulltext search relevance to make one field more 'valuable' than another?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USFrank Tudor
    1. USmintywalker
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COWorks well and makes sense. Thanks!
      singulars
      1. PO
      1. USBretticus
    2. COI could not seem to get this to work (perhaps because I had not added the third index), but changing the where condition to: rel1 > 0 OR rel2 > 0 solved my problem so thanks.
      singulars
      1. PO
      1. USUltimate Gobblement
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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