我坚持在MySQL数据库中的InnoDB表上查询 . 我需要在两个文本字段上查找基于全文搜索的订单，这两个文本字段包含json编码文本中的订单和客户详细信息 . 这是表模式：
+--------------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| user_id | int(11) | NO | MUL | NULL | |
| comment | text | NO | | NULL | |
| modified | datetime | NO | | NULL | |
| created | datetime | NO | MUL | NULL | |
| items | mediumtext | NO | MUL | NULL | |
| addressinfo | text | NO | | NULL | |
+--------------+------------+------+-----+---------+----------------+
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| orders | 0 | PRIMARY | 1 | id | A | 69144 | NULL | NULL | | BTREE | | |
| orders | 1 | user_id | 1 | user_id | A | 45060 | NULL | NULL | | BTREE | | |
| orders | 1 | created | 1 | created | A | 69240 | NULL | NULL | | BTREE | | |
| orders | 1 | search | 1 | items | NULL | 69240 | NULL | NULL | | FULLTEXT | | |
| orders | 1 | search | 2 | addressinfo | NULL | 69240 | NULL | NULL | | FULLTEXT | | |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
该表有大约150.000行 . 它在items和addressinfo列上有一个全文索引 .
以下是查询：
SELECT
id
FROM
orders
WHERE
MATCH (items, addressinfo) AGAINST (
'+simon* +white* ' IN BOOLEAN MODE
)
ORDER BY
id DESC
LIMIT
20
这是EXPLAIN结果：
+----+-------------+--------+------------+----------+---------------+--------+---------+-------+------+----------+---------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+----------+---------------+--------+---------+-------+------+----------+---------------------------------------------------+
| 1 | SIMPLE | orders | NULL | fulltext | search | search | 0 | const | 1 | 100.00 | Using where; Ft_hints: no_ranking; Using filesort |
+----+-------------+--------+------------+----------+---------------+--------+---------+-------+------+----------+---------------------------------------------------+
在大型结果集上，查询大约需要30秒才能在标准LAMP VM上进行处理 .
没有订购
ORDER BY id DESC
查询在大约0.6秒内处理得更快 .
EXPLAIN结果的唯一区别是更快的查询中缺少“使用filesort” . 测量查询说，98％的处理时间（27秒）用于“创建排序索引” .
有没有办法在合理的处理时间（不到一秒）内使用ORDER BY在此表上进行全文搜索？
我已经尝试了不同的方法，将列按列放入全文索引（text_id为TEXT列），没有运气 . 从这里开始的方法：How to make a FULLTEXT search with ORDER BY fast?也不会更快 .
由于应用程序在共享主机上运行，因此我在优化MySQL ini值或内存值方面非常有限 .
非常感谢！
1 回答
使用已交付的表时，您可能会获得一些时间 . 试试吧 .
Query