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 |
+----------+------------+------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
Best Answer
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.