Fulltext index not matching column list on mysql

user_10338467 0
Viewed 483

I use contao 4.9 and have problems with a view that only arise when using mysql 5.7.35 instead of mariadb. The query that creates the view is the following:

CREATE
OR REPLACE VIEW `tl_news4ward_articleWithTags` AS
SELECT
tl_news4ward_article.*,
GROUP_CONCAT(tag) AS tags
FROM
tl_news4ward_article
LEFT OUTER JOIN tl_news4ward_tag ON (
    tl_news4ward_tag.pid = tl_news4ward_article.id
)
GROUP BY
tl_news4ward_article.id

The interesting part of the tl_news4ward_article table was created as follows:

CREATE TABLE `tl_news4ward_article` (
`title` varchar(255) NOT NULL DEFAULT '',
`keywords` text,
`description` text,
 PRIMARY KEY (`id`),
 KEY `pid` (`pid`),
 KEY `alias` (`alias`)
) ENGINE=MyISAM AUTO_INCREMENT=53 DEFAULT CHARSET=utf8

And tl_news4ward_tag:

CREATE TABLE `tl_news4ward_tag` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`pid` int(10) unsigned NOT NULL DEFAULT '0',
`tstamp` int(10) unsigned NOT NULL DEFAULT '0',
`tag` varchar(255) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `pid` (`pid`),
FULLTEXT KEY `tag` (`tag`)

) ENGINE=MyISAM AUTO_INCREMENT=103 DEFAULT CHARSET=utf8

When I run the query SELECT MATCH (keywords,title,description) AGAINST (' something1 something2' IN BOOLEAN MODE) AS score FROM tl_news4ward_article it just works, but if I run SELECT MATCH (keywords,title,description) AGAINST (' something1 something2' IN BOOLEAN MODE) AS score FROM tl_news4ward_articleWithTags I get an error:

#1191 - Can't find FULLTEXT index matching the column list

I can provide more information if needed. It just works on Mariadb.

DB Fiddle:

https://www.db-fiddle.com/f/3LN1cAM6aoohaB4a1q6oWb/1

EDIT2:

The code comes from this Contao module: https://github.com/psi-4ward/news4ward/issues/106 we think is supposed to work with MySQL

EDIT3:

More clear compare-fiddle: https://dbfiddle.uk/?rdbms=mysql_5.7&rdbms2=mariadb_10.3&fiddle=2f1ab88e65a8acb3bb992a1cf6fb4101

EDIT4:

The above query is simplified - as you can see in the Contao module, the tags column should be included in the score and match as well.

mysql
4 Answers

  • The error comes from not having an index on the combination of the 3 columns:

      FULLTEXT(keywords,title,description)

  • You should move to InnoDB, which now has FULLTEXT. The two tables may need to use the same Engine. Caution: There are several details about InnoDB's FULLTEXT that are different than MyISAM's. Here is a list: http://mysql.rjweb.org/doc.php/myisam2innodb#fulltext

  • MySQL's VIEWs are mostly syntactic sugar. While I have not encountered your specific problem, it seems clear that VIEW is getting in the way.

  • Did you try explicitly specifying the two 'algorithms' for expanding Views? https://dev.mysql.com/doc/refman/5.7/en/view-algorithms.html

  • If you are having performance problems with the query (once it works), it may be because of doing the JOIN before doing the filtering. If you encounter such, we can discuss that in a separate Question.

(From fiddle)

I prefer this way of getting the record, plus tags:

SELECT  a.*,
        ( SELECT GROUP_CONCAT(tag)
              FROM tl_news4ward_tag AS t
              WHERE t.pid = a.id
        ) AS tags
    FROM  tl_news4ward_article AS a

But there is no way (with the current schema) to include tags in the MATCH.

4-col FT

If you decide that you must have tags in the FULLTEXT index, then the commalist of tags in the main table and get rid of the tags table. Then you need some stored procedures/functions to handle add-tag, del-tag, etc. Tip: FIND_IN_SET('foo', tags) will come in handy for testing for a particular tag.

user_17668310

You can't use full text indexes on view.

MySQL doesn't allow any form of indexes on a view, just on it's underlying tables. The reason for this is because MySQL only materializes a view when you select from it, due to the possibility of the underlying tables changing data all the time. If you had a view that returned 10 million rows, you'd have to apply a full text index to it every time you selected from it, and that takes a lot of time.

So the only chance you get is

add

ALTER TABLE tl_news4ward_article  
ADD FULLTEXT(keywords,title,description)

SELECT * FROM   tl_news4ward_articleWithTags t1  INNER JOIN ( SELECT MATCH (keywords,title,description) AGAINST (' something1 something2' IN BOOLEAN MODE) AS score ,id FROM tl_news4ward_article ) t2
ON t1.id =  t2.id;

id | title     | keywords  | description | tags | score | id
-: | :-------- | :-------- | :---------- | :--- | ----: | -:
 1 | something | something | something   | null |     0 |  1

db<>fiddle here

user_51935360

Views do not have indexes, so index hints do not apply. Use of index hints when selecting from a view is not permitted.

user_128885720

This actually might be a MySQL 5.7 regression.

The linked Contao Module's code is 8 years (2014) old:

https://github.com/psi-4ward/news4ward_related/blame/master/RelatedHelper.php#L18

MySQL 5.7 was releasted 6 years (2015) ago.

The code broke was working in 5.6 but does no longer in 5.7

https://dbfiddle.uk/?rdbms=mysql_5.6&rdbms2=mysql_5.7&fiddle=1d875703c4781dad878e64943057de5a

Not sure if this s a bug in MySQL 5.7 or an intended removal of that functionality.

Lowset friction solution might really be to update the Contao module and remove the usage of the VIEW in the queries.

user_2885680
Related