[MDEV-7523] Behaviour fulltext search MyISAM vs. InnoDB (MariaDB 10.0.15) Created: 2015-01-28  Updated: 2015-06-16  Resolved: 2015-06-16

Status: Closed
Project: MariaDB Server
Component/s: Full-text Search, Storage Engine - InnoDB
Affects Version/s: 10.0.15
Fix Version/s: 10.0.20

Type: Bug Priority: Major
Reporter: Florian Gusinde Assignee: Sergei Golubchik
Resolution: Fixed Votes: 0
Labels: upstream-fixed, verified
Environment:

Debian 7 x86_64

Issue Links:
Blocks
is blocked by MDEV-8290 10.0.20 merge Closed
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.

 Comments   
Comment by Florian Gusinde [ 2015-01-29 ]

Additional info: Testing on Aria Engine works, too.

Comment by Elena Stepanova [ 2015-02-02 ]

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);
 
 

Comment by Florian Gusinde [ 2015-02-02 ]

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

Comment by Elena Stepanova [ 2015-02-03 ]

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

Comment by Elena Stepanova [ 2015-02-19 ]

Fixed in 5.7.7, 5.8.0

Comment by Sergei Golubchik [ 2015-02-20 ]

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

Comment by Jasper Schaart [ 2015-05-06 ]

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

Comment by Florian Gusinde [ 2015-05-08 ]

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

Comment by Elena Stepanova [ 2015-06-09 ]

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.

Generated at Thu Feb 08 07:20:15 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.