如何實現複雜的搜尋功能？

下面是我的表結構：

CREATE TABLE `post` (
 `postId` bigint(20) NOT NULL AUTO_INCREMENT,
 `userId` mediumint(8) unsigned NOT NULL,
 `areaId` smallint(5) unsigned DEFAULT NULL,
 `postTitle` varchar(120) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 `postPrice` int(10) unsigned NOT NULL,
 `availableFrom` date DEFAULT NULL,
 `postCreateDate` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
 `postCreateIp` varbinary(16) DEFAULT NULL,
 `postUpdateDate` timestamp NULL DEFAULT NULL,
 `postUpdateIp` varbinary(16) DEFAULT NULL,
 `fullAddress` varchar(127) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
 `floor` tinyint(3) unsigned DEFAULT NULL,
 `room` tinyint(3) unsigned DEFAULT NULL,
 `bath` tinyint(3) unsigned DEFAULT NULL,
 `size` smallint(5) unsigned DEFAULT NULL,
 `postContactNo` varchar(36) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 `des` varchar(511) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
 `imageUrl` varchar(127) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
 `postType` tinyint(4) unsigned NOT NULL DEFAULT '0' COMMENT '0>for_rent\n1>need_rent',
 PRIMARY KEY (`postId`),
 KEY `post_userId_postCreateDate_index` (`userId`,`postCreateDate`),
 KEY `post_areaId_postType_postId_index` (`areaId`,`postType`,`postId` DESC),
 CONSTRAINT `post_join_area_areaId_fk` FOREIGN KEY (`areaId`) REFERENCES `join_area` (`areaId`),
 CONSTRAINT `post_user_userId_fk` FOREIGN KEY (`userId`) REFERENCES `user` (`userId`)
) ENGINE=InnoDB AUTO_INCREMENT=20004 DEFAULT CHARSET=utf8

我想為這個表添加搜尋功能。會有以下條件：

1. 我可以按與行匹配的文本進行搜尋，postTitle並且 des.
2. 我也可以按行的許多條件過濾這些結果 areaId, postType postPrice, availableFrom, floor, room, bath, size。

下面是一個例子：

select *
from post
WHERE areaId = 29
 AND postType in (0, 1)
 AND (postPrice > 0 AND postPrice < 10000000)
 AND availableFrom = '2019-06-26'
 AND (floor >= 0 and floor <= 6)
 AND (room >= 0 and room <= 20)
 AND (bath >= 0 and bath <= 10)
 AND (size >= 0 and size <= 20000);

現在我不確定如何有效地實現它。我可以考慮以下方式：

1. postTitle首先，我將查詢與 行匹配的數據，此查詢將使用我將在行上創建的desMySQL 全文索引。然後我將對這些行的結果應用過濾器，但這不會覆蓋任何索引，因為這將使用臨時表。postTitle``des``areaId, postType postPrice, availableFrom, floor, room, bath, size
2. 其次，我可以通過組合以下所有條件來編寫查詢：

select *
from post
WHERE (postTitle like '%post%' or des like '%test%')
 AND areaId = 29
 AND postType in (0, 1)
 AND (postPrice > 0 AND postPrice < 10000000)
 AND availableFrom = '2019-06-26'
 AND (floor >= 0 and floor <= 6)
 AND (room >= 0 and room <= 20)
 AND (bath >= 0 and bath <= 10)
 AND (size >= 0 and size <= 20000);

但我不確定如何使這個查詢有效。如何為此查詢創建索引。請建議我如何設計此搜尋功能，或者我需要做什麼才能使此查詢有效？

使用全文：

FULLTEXT(postTitle, des)

和

AND MATCH(postTitle, des) AGAINST("+post +test" IN BOOLEAN MODE)

建構並選擇性地省略任何未請求的測試。也就是說，如果只有 20 個房間，則不要包括此行：

AND (room >= 0 and room <= 20)

不要打擾意志bath >= 0，因為它是UNSIGNED。

收集典型的查詢。通過詢問“使用者”他們將詢問什麼或通過即時收集查詢。 然後建構“最佳”索引。

但是，首先請注意，如果MATCH包含該子句，它將是唯一使用的索引。也就是說，對於使用者沒有在 postTitle 和 des 上進行測試的情況，那麼我們可以查看索引。

澄清：它將首先使用 FT 索引，然後過濾任何其他WHERE子句，但沒有INDEXes. 假設是 FT 過濾到很少的行，後面的過濾沒有問題。

上例中僅有的兩個有用的列是帶有=. 所以：

INDEX(areaId, availableFrom)

但是，在查看使用者活動後，您可能會發現其他人。

另請參閱彈性搜尋。

引用自：https://dba.stackexchange.com/questions/243785