Mysql
與其他過濾器結合使用時，MySQL InnoDB 全文搜尋非常慢
我有一個包含 2300 萬條記錄的數據庫，在 HDD 上佔用了大約 16GB。具有 4Gb RAM 的 64 位作業系統。
例如查詢
SELECT * FROM robjects WHERE MATCH(type) AGAINST ('commercial' IN BOOLEAN MODE) LIMIT 10工作正常。
但是當我嘗試添加更多過濾器時
SELECT * FROM robjects WHERE MATCH(type) AGAINST ('commercial' IN BOOLEAN MODE) AND ID > 20000 LIMIT 10大約需要 2 分鐘。當我嘗試將它與 AREA BETWEEN 結合使用時，查詢將永遠持續並最終出現記憶體不足的錯誤。
問題是是否存在任何適當的解決方法。或者唯一的出路是將其導出到 NoSQL 解決方案，如 ElasticSearch 或 MongoDB。
關於數據庫結構。只有一個包含大量長文本欄位的表：
+-----------------------------+---------------+------+-----+---------+----------------+ | 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 | | +-----------------------------+---------------+------+-----+---------+----------------+它的索引
+----------+------------+------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+ | 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 | +----------+------------+------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
數據庫結構表現不佳。長文本的文本欄位應該是數字\列舉，一些數據應該移動到新表等。我想我需要先將數據庫重構為多個表。
關於什麼
SELECT * FROM ( SELECT * FROM robjects WHERE MATCH(type) AGAINST ('commercial' IN BOOLEAN MODE) ) x WHERE ID > 20000 LIMIT 10?