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

by Восилей   Last Updated September 07, 2018 08:06 AM

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     |
+----------+------------+------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+


Answers 1


What about

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

?

Akina
Akina
September 07, 2018 07:57 AM

Related Questions




fulltext search in innodb is very slow

Updated March 28, 2017 09:06 AM

FullText search using only some of columns in index

Updated August 14, 2015 18:02 PM