I was too late to realize that a query like the following, which combines FULLTEXT match and another where condition is not scalable.
SELECT * FROM mytable
WHERE MATCH(message) AGAINST (‘inception’) AND is_member = 1
(In this table message is FULLTEXT indexed and the boolean type column is_member is B-Tree indexed)
The query is not scalable, because MySql can’t use both the indices (It can use only one index per table). And, we can’t index both these columns together too.
The solution is pretty simple. While inserting message into the table, append an unique value to identify the record as member’s one. I’d do something like this,
INSERT INTO mytable(message, is_member)
VALUES(‘Just saw inception ismember’, 1)
Now, MySql indexes the word “ismember” along with other texts in the record, and we can use a query like the following, without the extra WHERE condition,
SELECT * FROM mytable
WHERE MATCH(message) AGAINST (‘+inception +ismember’)
This query will return only the member records that matched “inception”. This query uses just one index and runs pretty faster than the previous one. The difference can be seen, when you have millions of records in your table.
This example showed a boolean column. But this can be extended to columns of any type. We just have to make sure to append unique identifiers to the FULLTEXT indexed column.
