Full Text Searching & Indexing

MySQL & MariaDB Other MySQL® Questions
1

Hi,

Please forgive me if this forum is not the right place for my question.

I’m testing Percona 5.6 which has addopted Full Text Search on InnoDB as MySQL 5.6 feature. Let’s say I have table “mytable” with “a” and “b” columns as integer and “x” and “y” columns as varchar. Columns “a” and “b” are indexed by using BTREE index, columns “x” and “y” are indexed by using FULLTEXT index. Which one of following order of WHERE clause is better?

SELECT * FROM mytable WHERE MATCH(x) AGAINST (‘txtx’ IN BOOLEAN MODE) AND MATCH(y) AGAINST (‘txty’ IN BOOLEAN MODE) AND a = vala AND b = valb;

SELECT * FROM mytable WHERE a = vala AND b = valb AND MATCH(x) AGAINST (‘txtx’ IN BOOLEAN MODE) AND MATCH(y) AGAINST (‘txty’ IN BOOLEAN MODE);

For columns “a” and “b”, I think it is better for using multicolumns BTREE index since the order of WHERE clause on my query will always same, isn’t it? What’s about the FTS index for columns “x” and “y”, should I use multicolumns FULLTEXT index too or separate FULLTEXT index for each column?

Best regards,

2

I know this out of the question, but I need to say - if you need fulltext search for your application, do not use MySQL for it. Solr & Sphinx do it way to much better in terms of performance and possibilies. Sphinx additionally is easy to understand for DB-guys, becuase basically all what you need to configure it - provide SQL statments which get data for indexing