Mariadb – Match Against search not doing exact search

full-text-searchmariadb

I'm using MariaDB version 10.2.22 with a InnoDB engine, utf8 charset, it has one column that is indexed FULLTEXT. The table itself is only 4.4 GB with avg row length at 255. When I do a query such as:

    SELECT m.id as id
    FROM 
    table1 m,
    table2 d
    where 
    m.id= d.id AND
    MATCH ([column]) AGAINST ('"CE20-2019"' in boolean mode)

I get a "SQL Error [128] [HY000]:Table handler out of memory". I suspect that the "-2019" is being searched or not included, I'm not sure, and that the found matches exceeds the memory allowed. The [column] is a "LongText" datatype.

I've tried changing the innodb_buffer_pool_size to 4 GB and then 8 GB, but still get this error.

(System has 24 GB of RAM, not sure if my comfortable changing it to 70% of RAM listed on their documentation found here: https://mariadb.com/kb/en/mariadb-memory-allocation/), since I'm on version 10.2.22, this innodb_buffer_pool_size variable is suppose to be dynamic so I don't have to restart the MariaDB service to restart Mysqld.

Any ideas what's causing the Table handler out of memory?

Best Answer

Plan A:

As you put the data into the table, change - to, say, _ so that such items will be on "word", not two. Do a similar change when constructing the AGAINST clause.

If necessary, make an extra column with that and do any other cleansing of the data -- Then index that column with FULLTEXT, but use the original table for output.

Plan B:

MATCH (col) AGAINST ('+CE20' in boolean mode)
AND col LIKE '%CE20-2019%'

Or, if you need a regexp to handle a letter/digit before:

MATCH (col) AGAINST ('+CE20' in boolean mode)
AND col REGEXP '\\bCE20-2019'

(That's for MySQL 8; use [[:<:]] instead of \\b for other versions. That is "word boundary" and should eliminate most of the 20 extra rows.)

That will run nearly as fast as the original query should have run.

Alas, this requires understanding the incoming data and making programmatic decisions as to how best to construct a fast query.

Note that the use of + when IN BOOLEAN MODE may be the 'right' thing to do for your app.

PROPOSED QUERY

SELECT * FROM test WHERE MATCH(tags)
AGAINST ('"+proj_7" "(invoice report)"' IN BOOLEAN MODE);

YOUR SAMPLE DATA LOADED

mysql> USE test
Database changed
mysql> DROP TABLE IF EXISTS test;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE `test` (
    ->   `tags` text NOT NULL,
    ->   FULLTEXT KEY `tags` (`tags`)
    -> );
Query OK, 0 rows affected (0.20 sec)

mysql> INSERT INTO test (tags) VALUES ("proj_7 type_invdtl year_15 month_04");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test (tags) VALUES ("proj_8 type_invdtl year_15 month_04");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test (tags) VALUES ("proj_10 type_invdtl year_15 month_04");
Query OK, 1 row affected (0.02 sec)

mysql> SELECT * FROM test;
+--------------------------------------+
| tags                                 |
+--------------------------------------+
| proj_7 type_invdtl year_15 month_04  |
| proj_8 type_invdtl year_15 month_04  |
| proj_10 type_invdtl year_15 month_04 |
+--------------------------------------+
3 rows in set (0.00 sec)

mysql>

PROPOSED QUERY EXECUTED

mysql> SELECT * FROM test WHERE MATCH(tags)
    -> AGAINST ('"+proj_7" "(invoice report)"' IN BOOLEAN MODE);
+-------------------------------------+
| tags                                |
+-------------------------------------+
| proj_7 type_invdtl year_15 month_04 |
+-------------------------------------+
1 row in set (0.00 sec)

mysql>

OTHER EXAMPLES

mysql> SELECT * FROM test WHERE MATCH(tags)
    -> AGAINST ('"+proj_7" "(type_invdtl year_15)"' IN BOOLEAN MODE);
+--------------------------------------+
| tags                                 |
+--------------------------------------+
| proj_7 type_invdtl year_15 month_04  |
| proj_8 type_invdtl year_15 month_04  |
| proj_10 type_invdtl year_15 month_04 |
+--------------------------------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM test WHERE MATCH(tags)
    -> AGAINST ('"+proj_7" "(type_invdt2 year_15)"' IN BOOLEAN MODE);
+-------------------------------------+
| tags                                |
+-------------------------------------+
| proj_7 type_invdtl year_15 month_04 |
+-------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM test WHERE MATCH(tags) AGAINST ('"+proj_7 +type_invdtl" "+proj_7 +year15"' IN BOOLEAN MODE);
+-------------------------------------+
| tags                                |
+-------------------------------------+
| proj_7 type_invdtl year_15 month_04 |
+-------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM test WHERE MATCH(tags) AGAINST ('"+proj_7 +type_invdtl" "+proj_7 +year16"' IN BOOLEAN MODE);
+-------------------------------------+
| tags                                |
+-------------------------------------+
| proj_7 type_invdtl year_15 month_04 |
+-------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM test WHERE MATCH(tags) AGAINST ('"+proj_7 +type_invdt2" "+proj_7 +year16"' IN BOOLEAN MODE);
Empty set (0.00 sec)

mysql>

Best Answer

Related Solutions

MySQL Full Text search increase relevance for exact matches

Thesql fulltext – exact match or nothing

PROPOSED QUERY

YOUR SAMPLE DATA LOADED

PROPOSED QUERY EXECUTED

OTHER EXAMPLES

GIVE IT A TRY !!!

Related Question