0

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

3 Answers 3

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.

1
  • You want to say that 'commercial' value of the type field is exact value, not a substring??? Or this field contains a small list of possible values, and a part of them contains 'commercial'??? If so I recommend you to forget urgently about the FULLTEXT exists until you really understand the aims it exists for...
    – Akina
    Commented Sep 7, 2018 at 8:57
0

What about

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

?

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.