I am struggling to grok this query, which searches a MyIASAM table. The code no longer runs when the table is converted to InnoDB so I am trying to rewrite the queries.
The confusing bit, which is impossible to google for, is the ‘>>>’ operator.
The query returns a list of rankings based on the ‘relevance’ of the title and content (crank and trank)
Here’s the query:
SELECT ID, MATCH(post_title)
AGAINST ('>>>("weld") >(weld) ' IN BOOLEAN MODE) as trank,
MATCH(post_content_filtered) AGAINST ('>>("weld") (weld) ' IN BOOLEAN MODE) as crank
FROM wp_posts,wp_postmeta
WHERE wp_posts.post_type='ewimc_article'
AND wp_posts.post_status IN ('publish','private')
AND wp_posts.ID=wp_postmeta.post_id
AND wp_postmeta.meta_key='ewi_pub_year'
AND ( ( (MATCH(post_title)
AGAINST ('+weld' IN BOOLEAN MODE)
OR MATCH(post_content_filtered)
AGAINST ('+weld' IN BOOLEAN MODE)) ) )
ORDER BY trank + crank DESC
So, what are these mystery operators? Is there a replacement for them that will work in an InnoDB table?
Thanks.
>Solution :
that is used in a FULL TEXT INDEX from mysql and a search in booleanmode
> <
These two operators are used to change a word's contribution to the
relevance value that is assigned to a row. The > operator increases
the contribution and the < operator decreases it.
So that >>>("weld") means that weld allone higher impact then weld with other characters as part of it