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

Behaviour fulltext search MyISAM vs. InnoDB (MariaDB 10.0.15)

        Details

        • Sprint:
          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.

            Gliffy Diagrams

              Attachments

                Issue Links

                is blocked by

                Task - A task that needs to be done. MDEV-8290 10.0.20 merge

                • Blocker - Blocks development and/or testing work, production could not run.
                • Closed
                links to

                Web Link Bug #75755 - Fulltext search behaviour with MyISAM vs. InnoDB (wrong result with InnoDB)

                  Activity

                  Ascending order - Click to sort in descending order
                  Hide
                  Permalink
                  fgusinde Florian Gusinde added a comment -

                  Additional info: Testing on Aria Engine works, too.

                  Show
                  fgusinde Florian Gusinde added a comment - Additional info: Testing on Aria Engine works, too.
                  Hide
                  Permalink
                  elenst Elena Stepanova added a comment -

                  Thanks for the report and the test case.
                  The bug is also reproducible on current MySQL 5.6 (I assume it's a bug, I haven't found anything in MySQL documentation that would suggest that it's an expected behavior or a known limitation).

                  Our normal practice is to re-report upstream bugs at bugs.mysql.com. Are you willing to do it, or should we do it on your behalf?

                  The test case below is basically the same as in the description, just put together for MTR:

                  
--source include/have_innodb.inc

CREATE TABLE `content` (
  `content_id` int(11) NOT NULL,
  `title` tinytext,
  `story` text,
  PRIMARY KEY (`content_id`),
  FULLTEXT KEY `key_all` (`title`,`story`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
;

insert into content values 
(1,'','VQLTITThe VQLTITgood VQLTITwife'),
(2,'','VQLTITThe VQLTITgood VQLTITwife');

select * from content where MATCH (`title`,`story`) AGAINST ('("VQLTITThe VQLTITgood VQLTITwife")' IN BOOLEAN MODE);

alter table content engine=MyISAM;
select * from content where MATCH (`title`,`story`) AGAINST ('("VQLTITThe VQLTITgood VQLTITwife")' IN BOOLEAN MODE);
                  Show
                  elenst Elena Stepanova added a comment - Thanks for the report and the test case. The bug is also reproducible on current MySQL 5.6 (I assume it's a bug, I haven't found anything in MySQL documentation that would suggest that it's an expected behavior or a known limitation). Our normal practice is to re-report upstream bugs at bugs.mysql.com. Are you willing to do it, or should we do it on your behalf? The test case below is basically the same as in the description, just put together for MTR: --source include/have_innodb.inc CREATE TABLE `content` ( `content_id` int(11) NOT NULL, `title` tinytext, `story` text, PRIMARY KEY (`content_id`), FULLTEXT KEY `key_all` (`title`,`story`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ; insert into content values (1,'','VQLTITThe VQLTITgood VQLTITwife'), (2,'','VQLTITThe VQLTITgood VQLTITwife'); select * from content where MATCH (`title`,`story`) AGAINST ('( "VQLTITThe VQLTITgood VQLTITwife" )' IN BOOLEAN MODE); alter table content engine=MyISAM; select * from content where MATCH (`title`,`story`) AGAINST ('( "VQLTITThe VQLTITgood VQLTITwife" )' IN BOOLEAN MODE);
                  Hide
                  Permalink
                  fgusinde Florian Gusinde added a comment -

                  Please re-report this bug to bugs.mysql.com so it will take the right way. Thank you!

                  Show
                  fgusinde Florian Gusinde added a comment - Please re-report this bug to bugs.mysql.com so it will take the right way. Thank you!
                  Hide
                  Permalink
                  elenst Elena Stepanova added a comment -

                  Filed as http://bugs.mysql.com/bug.php?id=75755

                  Show
                  elenst Elena Stepanova added a comment - Filed as http://bugs.mysql.com/bug.php?id=75755
                  Hide
                  Permalink
                  elenst Elena Stepanova added a comment -

                  Fixed in 5.7.7, 5.8.0

                  Show
                  elenst Elena Stepanova added a comment - Fixed in 5.7.7, 5.8.0
                  Hide
                  Permalink
                  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

                  Show
                  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
                  Hide
                  Permalink
                  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 ('(" ? ")') ?

                  Show
                  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 ('(" ? ")') ?
                  Hide
                  Permalink
                  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

                  Show
                  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
                  Hide
                  Permalink
                  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.

                  Show
                  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

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

                      Dates

                      • Created:
                        Updated:
                        Resolved:

                        Agile