Can you use multiple types of indexes in one composite index

Question

I've built a covering index for a pretty big query which has already halved the select time.

Now, all the columns are just INT(11)'s so there isn't a problem there but one of the columns is a VARCHAR(255) whose performance I'm able to improve by using a FULLTEXT index.

Right now I'm forcing my query to use my covering index as somehow the optimizer doesn't think my index would be faster even though it is, but now I'm wondering if it's possible to specify which indexing algorithm to use per column on the composite index so that I can do:

id INT(11) - BTREE

code VARCHAR(255) - FULLTEXT

col3 INT(11) - BTREE

col4 INT(11) - BTREE

col5 INT(11) - BTREE

col6 INT(11) - BTREE

If I preview the SQL for adding the index on the table, at the end it says USING BTREE; so I was wondering if it's possible to specify it on a per-column basis

Answer 1

FULLTEXT and BTree cannot be mixed in a single composite index.

Please provide SHOW CREATE TABLE and the SELECT for further discussion of alternatives.

Always(?), the Optimizer will pick a FULLTEXT index over any other. There are a few cases where complicating the query with a subquery can make it run faster. (But let's see what you are doing.)