Mysql: fulltext and normal index

Question

I would like to know if it is OK to have both a fulltext index and a "normal" index on the same column (varchar) of a table in MySQL

If my understanding is correct normal index will be used for queries using "=" and "like" and the fulltext will only be used when i do "match()" ?

Thank you for your help!

Thomas G · Accepted Answer · 2017-10-27 10:40:11Z

4

Yes it is allowed to have several indexes on the same column, and while it is most of the time useless, it makes perfect sense in your case.

MySQL will indeed use your FULLTEXT index when querying using MATCH...AGAINST syntax, and use the normal index for the rest.

answered Oct 27, 2017 at 10:40

Thomas G

10.3k7 gold badges33 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

sashok_bg Over a year ago

thank you for your answer, is there a place in the MySQL doc where they mention this behavior ?

Thomas G Over a year ago

not really but its just a logical assumption: When you have several indexes on the same column, it's the MySQL optimizer which choose the right one to use. If it doesnt use the right one, you can force the index you want with HINTS. However for FULLTEXT indexes, you should explicitly code your queries with the MATCH...AGAINST syntax because that tells the optimizer to use the corresponding FULLTEXT index. So in your case it will just do exactly what you want it to do.

sashok_bg Over a year ago

Our DBA has just confirmed what you have said as well :)

Thomas G Over a year ago

@sashok_bg c'est rassurant pour l'entreprise ;)

Collectives™ on Stack Overflow

Mysql: fulltext and normal index

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related