I have a FULLTEXT INDEX on multiple columns in table bar of database foo.
The output of mysql -u root --password=pw -e "SHOW INDEX FROM foo.bar;" is:
+-------+------------+---------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+---------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| bar | 0 | PRIMARY | 1 | version_id | A | 153 | NULL | NULL | | BTREE | | |
| bar | 1 | malfunction_desc_id | 1 | malfunction_id | A | 153 | NULL | NULL | | BTREE | | |
| bar | 1 | created_by | 1 | created_by | A | 25 | NULL | NULL | | BTREE | | |
| bar | 1 | description | 1 | description | NULL | NULL | NULL | NULL | | FULLTEXT | | |
| bar | 1 | important_notice | 1 | important_notice | NULL | NULL | NULL | NULL | | FULLTEXT | | |
| bar | 1 | localization_text | 1 | localization_text | NULL | NULL | NULL | NULL | | FULLTEXT | | |
| bar | 1 | idx_ft_title | 1 | title | NULL | NULL | NULL | NULL | | FULLTEXT | | |
+-------+------------+---------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
But table bar countains 153 elements with different titles. So my question is why is Cardinality NULL here? Can you explain it?
I also tried to rebuild the index, but nothing changed:
mysql -u root --password=pw -e "ALTER TABLE foo.bar DROP INDEX title;"
mysql -u root --password=pw -e "ALTER TABLE foo.bar ADD FULLTEXT INDEX idx_ft_title (title);"
mysql -u root --password=pw -e "ANALYZE TABLE foo.bar;"
You seem to have 4 separate FULLTEXT indexes, one on each of 4 columns? Did you intend to have a single index on the combination of columns?
The cardinality of FULLTEXT is not computed and would not be used. The assumption is that an FT index is more useful than any other index. So, it is used first (when AND'd with something else); and any other WHERE clauses won't use INDEXes.