Fulltext index combined with normal index

Question

In my database, I have a table that contains a companyId, pointing to a company, and some text. I would like to do a FULLTEXT search, but as I always make requests against a specific companyId I'd like to use a composite key that combines my companyId and the fulltext index. Is there anyway to do that ? As I guess this is not possible, what is the optimal way to create indexes so that the following query is fastest ?

The request will always be

SELECT * FROM textTable 
WHERE companyId = ? (Possibly more conditions) AND 
  MATCH(value) AGAINST("example")

Should I create my indexes on integer columns normally and add one fulltext index ? or should I include the value column in the index ? Maybe both ?

Answer 1

Depending on how large the text field is, MySQL will store the data to disk, so including it in a compound index won't do you much good. I this scenario, the index should simply be the companyId, which will have a reference back to the PK, which will then parse each of the text fields.

The general rule of thumb is to filter early, so filtering first by companyId (int - 4 bytes) is preferable. Now, if the text field is small enough, you can add it as the second value in the index to prevent the second round-trip, but understand that this will impact INSERT performances significantly.

The best option for this type of scenario may be to use a NoSQL database to handle the text lookup, if the text fields are large; they're pretty exceptional with that type of job.