is a field in a fulltext key indexed and fast to use in join?

Question

I am currently working with a database that was auto generated by a tool (and is used in production)

(I will only speak about what is interesting for the question)

I have three tables : user, movie and userMovie.

the command show create table user return something like :

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  `other_field_1` varchar(255) DEFAULT NULL, -- not actual field name
  PRIMARY KEY (`id`),
  FULLTEXT KEY `SEARCH_USERS` (`username`,`other_field_1`)
)

the command show create table movie return something like :

CREATE TABLE `movie` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `link` varchar(255) DEFAULT NULL,
  `another_field_1` varchar(255) DEFAULT NULL, -- not actual field name
  `another_field_2` varchar(255) DEFAULT NULL, -- not actual field name
  PRIMARY KEY (`id`),
  FULLTEXT KEY `SEARCH_MOVIES` (`name`,`link`,`another_field_1`,`another_field_2`)
)

the command show create table userMovie return something like :

CREATE TABLE `userMovie` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(255) DEFAULT NULL,
  `user` int(11) DEFAULT NULL,
  `field1` varchar(255) DEFAULT NULL, -- not actual field name
  `field2` varchar(255) DEFAULT NULL, -- not actual field name
  `field3` varchar(255) DEFAULT NULL, -- not actual field name
  PRIMARY KEY (`id`),
  FULLTEXT KEY `SEARCH_USER_MOVIE` (`Name`,`field1`,`field2`,`field3`)
)

Obviously, there is several issue with this code, the main ones being :

• There is no foreign key,
• The field userMovie.Name contain the name of the movie, not the id

I'm well aware of the inconsistency risk, but I'm more ignorant about the potential performance issue. Especially, there is a lot of records in the userMovie table, and we have to join it quite often with the movie table (and the user table)

However, as userMovie.Name is in the "FULLTEXT KEY", does that mean it is indexed ? By the way, I think that only the tool previously mentioned had an use of this, and can probably be removed if needed.

I would want to know if there is a performance issue and ways to improve it. (It would also be awesome if the modification I'll be doing are "safe", as I don't want to break anything)

Answer 1

The column(s) in a FULLTEXT index are usable only for MATCH...AGAINST.

If you also want a BTree index on the column(s), provide a separate INDEX.

You can do

WHERE MATCH(`Name`,`field1`,`field2`,`field3`) AGAINST("...")
  AND field4 > 123

Or even

WHERE MATCH(`Name`,`field1`,`field2`,`field3`) AGAINST("...")
  AND name = 'abc'

However, this second format makes little sense. Usually a column is searched by either FULLTEXT or a regular index, not both.

What is the intent of the table userMovie? The name sounds like a many-to-many mapping table (eg, which movies each user has watched), but the columns do not reflect that.

To address a "performance issue", we need to see the SELECTs -- they have performance issues, not the schema. They guide what indexes are useful.