MySQL FTS implementation

Question

I work on a ticket system in PHP/MySQL. We have implemented mysql FTS in boolean mode, to provide the search feature to users. User enters search string and we present her a list of issues.

Below is the sample database schema:

issues table - (id, title, description)
It has fts index (title, description)

issue_comments table - (id, issue_id, comment)
It has fts index (comment)

Each issue can have multiple comments. So there can be multiple rows in issue_comments table corresponding to a row in issues table

Say user enters a search string - alpha beta

php script breaks the string in individual words, append a '+' sign before every word and executes the below query.

select id as issue_id from issues where match(title, description) against ('+alpha +beta' in      boolean mode)   
    union  
select id as issue_id from issues inner join issue_comments on        issue.id=issue_comments.issue_id where match(comment) against ('+alpha +beta' in boolean mode)

This searches all issues
having alpha and beta in (title,description)
or having both alpha and beta in one of the comments of the issue.

But this setup has a limitation
I want an issue to come in search result if alpha is present in the issue title and beta is present in one of its comments. This is not possible with above setup.
How can I achieve this?

Answer 1

I haven't tried this in real life yet, but specifying multiple columns should work in boolean mode as well:

match (title,comment) against...

there is no explicit mention of this, but the docs talk about rows that are searched, not columns.