Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-7523

Behaviour fulltext search MyISAM vs. InnoDB (MariaDB 10.0.15)

Details

    • 10.0.20

    Description

      I'm currently trying to migrate a database from MySQL 5.5 to MariaDB 10.x because the application (not mine) is using fulltext search in MyISAM and we have a lot of trouble with table locks. I have a testing system and stripped down information here:

      MariaDB [db]> alter table content engine=MyISAM;
      Query OK, 276331 rows affected (8.63 sec)
      Records: 276331  Duplicates: 0  Warnings: 0
       
      MariaDB [db]> select count(*) from content where MATCH (shortdescription,title,keywords,story) AGAINST ('("VQLTITThe VQLTITgood VQLTITwife")' IN BOOLEAN MODE);
      +----------+
      | count(*) |
      +----------+
      |        2 |
      +----------+
      1 row in set (0.00 sec)
       
      MariaDB [db]> alter table content engine=InnoDB;
      Query OK, 276331 rows affected (51.86 sec)
      Records: 276331  Duplicates: 0  Warnings: 0
       
      MariaDB [db]> select count(*) from content where MATCH (shortdescription,title,keywords,story) AGAINST ('("VQLTITThe VQLTITgood VQLTITwife")' IN BOOLEAN MODE);
      +----------+
      | count(*) |
      +----------+
      |        0 |
      +----------+
      1 row in set (0.02 sec)
       
      MariaDB [db]> select count(*) from content;
      +----------+
      | count(*) |
      +----------+
      |   276331 |
      +----------+
      1 row in set (0.06 sec)
       
      MariaDB [db]> explain select count(*) from content where MATCH (shortdescription,title,keywords,story) AGAINST ('("VQLTITThe VQLTITgood VQLTITwife")' IN BOOLEAN MODE);
      +------+-------------+---------+----------+---------------+---------+---------+------+------+-------------+
      | id   | select_type | table   | type     | possible_keys | key     | key_len | ref  | rows | Extra       |
      +------+-------------+---------+----------+---------------+---------+---------+------+------+-------------+
      |    1 | SIMPLE      | content | fulltext | key_all       | key_all | 0       |      |    1 | Using where |
      +------+-------------+---------+----------+---------------+---------+---------+------+------+-------------+
      1 row in set (0.00 sec)
       
      MariaDB [db]> show create table content\G;
      *************************** 1. row ***************************
             Table: content
      Create Table: CREATE TABLE `content` (
        `content_id` int(11) NOT NULL AUTO_INCREMENT,
        `keywords` text,
        `title` tinytext,
        `shortdescription` text,
        `story` text,
        PRIMARY KEY (`content_id`),
        UNIQUE KEY `UX_origin_id_content_id` (`content_id`),
        FULLTEXT KEY `keywords` (`keywords`),
        FULLTEXT KEY `key_all` (`keywords`,`title`,`shortdescription`,`story`)
      ) ENGINE=InnoDB AUTO_INCREMENT=10375362 DEFAULT CHARSET=latin1
      1 row in set (0.00 sec)
       
      ERROR: No query specified
       

      MariaDB version is 10.0.15-MariaDB-1~trusty.

      As you can see there's a different behaviour on MyISAM (count=2) and InnoDB (count=0). The reason for that is down in the fulltext index:

      MariaDB [db]> alter table content engine=MyISAM;
      Query OK, 276331 rows affected (8.73 sec)
      Records: 276331  Duplicates: 0  Warnings: 0
       
      MariaDB [db]> select * from content where MATCH (shortdescription,title,keywords,story) AGAINST ('("VQLTITThe VQLTITgood VQLTITwife")' IN BOOLEAN MODE);
      +------------+----------+-------+------------------+---------------------------------+
      | content_id | keywords | title | shortdescription | story              |
      +------------+----------+-------+------------------+---------------------------------+
      |     329522 |          |       |                  | VQLTITThe VQLTITgood VQLTITwife |
      |     329523 |          |       |                  | VQLTITThe VQLTITgood VQLTITwife |
      +------------+----------+-------+------------------+---------------------------------+
      2 rows in set (0.00 sec)
       
      MariaDB [db]> alter table content engine=InnoDB;
      Query OK, 276331 rows affected (36.91 sec)
      Records: 276331  Duplicates: 0  Warnings: 0
       
      MariaDB [db]> select * from content where MATCH (shortdescription,title,keywords,story) AGAINST ('("VQLTITThe VQLTITgood VQLTITwife")' IN BOOLEAN MODE);
      Empty set (0.01 sec)
       
      MariaDB [db]> update content set keywords='123', title='234', shortdescription='345';
      Query OK, 276331 rows affected (16.22 sec)
      Rows matched: 276331  Changed: 276331  Warnings: 0
       
      MariaDB [db]> select * from content where MATCH (shortdescription,title,keywords,story) AGAINST ('("VQLTITThe VQLTITgood VQLTITwife")' IN BOOLEAN MODE);
      +------------+----------+-------+------------------+---------------------------------+
      | content_id | keywords | title | shortdescription | story              |
      +------------+----------+-------+------------------+---------------------------------+
      |     329522 | 123      | 234   | 345              | VQLTITThe VQLTITgood VQLTITwife |
      |     329523 | 123      | 234   | 345              | VQLTITThe VQLTITgood VQLTITwife |
      +------------+----------+-------+------------------+---------------------------------+
      2 rows in set (0.13 sec)
       
      MariaDB [db]> update content set keywords=NULL, title=NULL, shortdescription=NULL;
      Query OK, 276331 rows affected (10.43 sec)
      Rows matched: 276331  Changed: 276331  Warnings: 0
       
      MariaDB [db]> select * from content where MATCH (shortdescription,title,keywords,story) AGAINST ('("VQLTITThe VQLTITgood VQLTITwife")' IN BOOLEAN MODE);
      +------------+----------+-------+------------------+---------------------------------+
      | content_id | keywords | title | shortdescription | story              |
      +------------+----------+-------+------------------+---------------------------------+
      |     329522 | NULL     | NULL  | NULL             | VQLTITThe VQLTITgood VQLTITwife |
      |     329523 | NULL     | NULL  | NULL             | VQLTITThe VQLTITgood VQLTITwife |
      +------------+----------+-------+------------------+---------------------------------+
      2 rows in set (0.25 sec)
       
      MariaDB [db]> update content set keywords=NULL, title=NULL, shortdescription='';
      Query OK, 276331 rows affected (11.42 sec)
      Rows matched: 276331  Changed: 276331  Warnings: 0
       
      MariaDB [db]> select * from content where MATCH (shortdescription,title,keywords,story) AGAINST ('("VQLTITThe VQLTITgood VQLTITwife")' IN BOOLEAN MODE);
      Empty set (0.58 sec)

      So my conclusion is InnoDB having troubles with empty string ('') but not with NULL values in fulltext search. So I think it's an engine-related problem.

      Attachments

        Activity

          Fixed in 5.7.7, 5.8.0

          elenst Elena Stepanova added a comment - Fixed in 5.7.7, 5.8.0

          This bug should be fixed in MariaDB 10.0.18, that is supposed to have InnoDB 5.6.24

          serg Sergei Golubchik added a comment - This bug should be fixed in MariaDB 10.0.18, that is supposed to have InnoDB 5.6.24
          Jasper Jasper Schaart added a comment - - edited

          Also different with MariaDB Fulltext InnoDB index (Great work BTW), is that all columns must be in the Query instead a few all full text columns must be given. Otherwise an 'Can't find FULLTEXT index matching the column list' will be returned.

          BTW @Florian Gusinde Are you sure your query is right? With the double ('(" ? ")') ?

          Jasper Jasper Schaart added a comment - - edited Also different with MariaDB Fulltext InnoDB index (Great work BTW), is that all columns must be in the Query instead a few all full text columns must be given. Otherwise an 'Can't find FULLTEXT index matching the column list' will be returned. BTW @Florian Gusinde Are you sure your query is right? With the double ('(" ? ")') ?

          It's not "my" query but I think it is right. Double brackets are okay, I think.

          From my side bug is fixed, of course you need an exactly matching key, e.g.

          MariaDB [db_maxdome]> ALTER TABLE content engine=InnoDB;
          Query OK, 276331 rows affected (20.04 sec)             
          Records: 276331  Duplicates: 0  Warnings: 0
           
          MariaDB [db_maxdome]> select * from content where MATCH (`title`,`story`) AGAINST ('("VQLTITThe VQLTITgood VQLTITwife")' IN BOOLEAN MODE);
          ERROR 1191 (HY000): Can't find FULLTEXT index matching the column list
          MariaDB [db_maxdome]> ALTER TABLE content ADD FULLTEXT KEY(title, story);
          Query OK, 0 rows affected (8.06 sec)
          Records: 0  Duplicates: 0  Warnings: 0
           
          MariaDB [db_maxdome]> select * from content where MATCH (`title`,`story`) AGAINST ('("VQLTITThe VQLTITgood VQLTITwife")' IN BOOLEAN MODE);
          +------------+----------+-------+------------------+---------------------------------+
          | content_id | keywords | title | shortdescription | story                           |
          +------------+----------+-------+------------------+---------------------------------+
          |     329522 |          | NULL  | NULL             | VQLTITThe VQLTITgood VQLTITwife |
          |     329523 |          | NULL  | NULL             | VQLTITThe VQLTITgood VQLTITwife |
          +------------+----------+-------+------------------+---------------------------------+
          2 rows in set (0.01 sec)

          Thank you!

          Regards, Florian

          fgusinde Florian Gusinde added a comment - It's not "my" query but I think it is right. Double brackets are okay, I think. From my side bug is fixed, of course you need an exactly matching key, e.g. MariaDB [db_maxdome]> ALTER TABLE content engine=InnoDB; Query OK, 276331 rows affected (20.04 sec) Records: 276331 Duplicates: 0 Warnings: 0   MariaDB [db_maxdome]> select * from content where MATCH (`title`,`story`) AGAINST ( '("VQLTITThe VQLTITgood VQLTITwife")' IN BOOLEAN MODE); ERROR 1191 (HY000): Can 't find FULLTEXT index matching the column list MariaDB [db_maxdome]> ALTER TABLE content ADD FULLTEXT KEY(title, story); Query OK, 0 rows affected (8.06 sec) Records: 0 Duplicates: 0 Warnings: 0   MariaDB [db_maxdome]> select * from content where MATCH (`title`,`story`) AGAINST (' ( "VQLTITThe VQLTITgood VQLTITwife" )' IN BOOLEAN MODE); + ------------+----------+-------+------------------+---------------------------------+ | content_id | keywords | title | shortdescription | story | + ------------+----------+-------+------------------+---------------------------------+ | 329522 | | NULL | NULL | VQLTITThe VQLTITgood VQLTITwife | | 329523 | | NULL | NULL | VQLTITThe VQLTITgood VQLTITwife | + ------------+----------+-------+------------------+---------------------------------+ 2 rows in set (0.01 sec) Thank you! Regards, Florian

          This bug should be fixed in MariaDB 10.0.18, that is supposed to have InnoDB 5.6.24

          It's fixed in InnoDB plugin, which is 5.6.24, but not in XtraDB, which is still 5.6.23. Hopefully MariaDB 10.0.20 has a newer XtraDB.

          elenst Elena Stepanova added a comment - This bug should be fixed in MariaDB 10.0.18, that is supposed to have InnoDB 5.6.24 It's fixed in InnoDB plugin, which is 5.6.24, but not in XtraDB, which is still 5.6.23. Hopefully MariaDB 10.0.20 has a newer XtraDB.

          People

            serg Sergei Golubchik
            fgusinde Florian Gusinde
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.