MySQL InnoDB Full Text Search is very slow when combined with other filters

Question

I have a database with 23 million records which occupies about 16GB on HDD. 64-bit Operation System with 4Gb RAM.

For example the query

SELECT * FROM robjects
WHERE MATCH(type) AGAINST ('commercial' IN BOOLEAN MODE)
LIMIT 10

works fine.

But when I try to add more filters like

SELECT * FROM robjects
WHERE MATCH(type) AGAINST ('commercial' IN BOOLEAN MODE)
AND ID > 20000
LIMIT 10

takes about 2 minutes. When I try to combine it with AREA BETWEEN, the query takes forever and ends up with error that memory is not enough.

The question is whether any appropriate workaround exists or not. Or the only way out is to export this to NoSQL Solution like ElasticSearch or MongoDB.

About database structure. Just one table with a large amount of longtext fields:

+-----------------------------+---------------+------+-----+---------+----------------+
|            Field            |     Type      | Null | Key | Default |     Extra      |
+-----------------------------+---------------+------+-----+---------+----------------+
| ID                          | int(11)       | NO   | PRI | NULL    | auto_increment |
| Date_create                 | datetime      | NO   |     | NULL    |                |
| Kvartal                     | longtext      | YES  | MUL | NULL    |                |
| Cadnomer                    | longtext      | YES  | MUL | NULL    |                |
| Name                        | longtext      | YES  | MUL | NULL    |                |
| Area                        | decimal(18,2) | NO   | MUL | NULL    |                |
| Price                       | decimal(18,2) | NO   |     | NULL    |                |
| Status_object               | longtext      | YES  | MUL | NULL    |                |
| Type                        | longtext      | YES  | MUL | NULL    |                |
| Floor                       | longtext      | YES  | MUL | NULL    |                |
| Floors                      | longtext      | YES  |     | NULL    |                |
| Floors_underground          | longtext      | YES  |     | NULL    |                |
| Completion_construction     | longtext      | YES  |     | NULL    |                |
| Land_category               | longtext      | YES  |     | NULL    |                |
| Permitted_use               | longtext      | YES  |     | NULL    |                |
| Type_ownership              | longtext      | YES  |     | NULL    |                |
| Commissioning               | longtext      | YES  |     | NULL    |                |
| SpecialNotes                | longtext      | YES  |     | NULL    |                |
| OwnershipText               | longtext      | YES  | MUL | NULL    |                |
| EncumbranceText             | longtext      | YES  | MUL | NULL    |                |
| Date_ownership              | datetime      | YES  | MUL | NULL    |                |
| Date_encumbrance            | datetime      | YES  | MUL | NULL    |                |
| Date_update_rosreestr       | datetime      | YES  |     | NULL    |                |
| Date_cadastral_registration | datetime      | YES  |     | NULL    |                |
| Date_value                  | datetime      | YES  |     | NULL    |                |
| Date_cost                   | datetime      | YES  |     | NULL    |                |
| Date_approval               | datetime      | YES  |     | NULL    |                |
| Link                        | longtext      | YES  |     | NULL    |                |
+-----------------------------+---------------+------+-----+---------+----------------+

And it's indexes

+----------+------------+------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
|  Table   | Non_unique |     Key_name     | Seq_in_index |   Column_name    | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible |
+----------+------------+------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| robjects |          0 | PRIMARY          |            1 | ID               | A         |    21865410 | NULL     | NULL   |      | BTREE      |         |               | YES     |
| robjects |          1 | Date_ownership   |            1 | Date_ownership   | A         |      118426 | NULL     | NULL   | YES  | BTREE      |         |               | YES     |
| robjects |          1 | Date_encumbrance |            1 | Date_encumbrance | A         |        6083 | NULL     | NULL   | YES  | BTREE      |         |               | YES     |
| robjects |          1 | Area             |            1 | Area             | A         |      485091 | NULL     | NULL   |      | BTREE      |         |               | YES     |
| robjects |          1 | Name             |            1 | Name             | NULL      |    21866081 | NULL     | NULL   | YES  | FULLTEXT   |         |               | YES     |
| robjects |          1 | Kvartal          |            1 | Kvartal          | NULL      |    21866081 | NULL     | NULL   | YES  | FULLTEXT   |         |               | YES     |
| robjects |          1 | Cadnomer         |            1 | Cadnomer         | NULL      |    21866081 | NULL     | NULL   | YES  | FULLTEXT   |         |               | YES     |
| robjects |          1 | Status_object    |            1 | Status_object    | NULL      |    21866081 | NULL     | NULL   | YES  | FULLTEXT   |         |               | YES     |
| robjects |          1 | Type             |            1 | Type             | NULL      |    21866081 | NULL     | NULL   | YES  | FULLTEXT   |         |               | YES     |
| robjects |          1 | Floor            |            1 | Floor            | NULL      |    21866081 | NULL     | NULL   | YES  | FULLTEXT   |         |               | YES     |
| robjects |          1 | OwnershipText    |            1 | OwnershipText    | NULL      |    21866081 | NULL     | NULL   | YES  | FULLTEXT   |         |               | YES     |
| robjects |          1 | EncumbranceText  |            1 | EncumbranceText  | NULL      |    21866081 | NULL     | NULL   | YES  | FULLTEXT   |         |               | YES     |
+----------+------------+------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+

score 1 · Answer 1 · answered Sep 07 '18 at 08:43

1

The database structure is not well performed. The text fields that are long-text should be numbers\enums, some of data should by move to new tables etc. I think I need to refactor database to multiple tables first.

answered Sep 07 '18 at 08:43

Liam Kernighan

111
3

score 0 · Answer 2 · answered Sep 07 '18 at 07:57

0

What about

SELECT *
FROM ( SELECT * FROM robjects
       WHERE MATCH(type) AGAINST ('commercial' IN BOOLEAN MODE)
     ) x
WHERE ID > 20000
LIMIT 10

?

answered Sep 07 '18 at 07:57

Akina

20,750
2
20
22

score 0 · Answer 3 · answered Oct 04 '18 at 02:56

0

Don't use FULLTEXT when you don't need it.

SELECT * FROM robjects
    WHERE `type` = 'commercial'  -- the substantive change
      AND ID > 20000
    ORDER BY ID    -- LIMIT is meaningless without ORDER BY
    LIMIT 10

and add

INDEX(type, ID)

answered Oct 04 '18 at 02:56

Rick James

80,479
5
52
119

MySQL InnoDB Full Text Search is very slow when combined with other filters

3 Answers3