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

Behaviour fulltext search MyISAM vs. InnoDB (MariaDB 10.0.15)

    XMLWordPrintable

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

          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.