mysql搜索标题、描述和多行标签

Question

我有以下表格。

入口表描述

+-------------+-------------------+------+-----+---------+----------------+
| Field       | Type              | Null | Key | Default | Extra          |
+-------------+-------------------+------+-----+---------+----------------+
| id          | int(11) unsigned  | NO   | PRI | NULL    | auto_increment |
| title       | varchar(255)      | YES  |     | NULL    |                |
| slug        | varchar(255)      | YES  |     | NULL    |                |
| description | text              | YES  |     | NULL    |                |
| user_id     | int(10) unsigned  | NO   |     | NULL    |                |
| unsafe      | enum('0','1')     | NO   |     | NULL    |                |
| copyright   | enum('0','1')     | NO   |     | 0       |                |
| status      | enum('0','1','2') | NO   |     | 0       |                |
| date_add    | datetime          | NO   |     | NULL    |                |
+-------------+-------------------+------+-----+---------+----------------+

标签表描述

+-------------+---------------------+------+-----+---------+----------------+
| Field       | Type                | Null | Key | Default | Extra          |
+-------------+---------------------+------+-----+---------+----------------+
| id          | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| relation_id | int(10) unsigned    | NO   |     | NULL    |                |
| name        | varchar(255)        | NO   |     | NULL    |                |
+-------------+---------------------+------+-----+---------+----------------+

我想在 entry.title 和 tag.name 中搜索。这个查询的性能如何。

我继续使用全文。你好吗？

Answer 1

首先，您需要确保标题和名称字段都已编入索引(无法通过您发送的信息看到这一点。我假设 tag.relation_id 与 entry.id 相关。所以您可以这样搜索:

SELECT * FROM entry as e JOIN tags as t on e.id = t.relation_id WHERE e.title LIKE('%YOURSEARCH%') OR t.name LIKE('%YOURSEARCH%')