我有一张桌子tbdb.ratedat，10GB大小，8KW记录，结构：
mysql> DESC ratedat;
+-------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| trade_id | bigint(20) | NO | PRI | NULL | |
| trade_time | int(10) | NO | | NULL | |
| uid_buy | bigint(20) | NO | MUL | NULL | |
| uid_sell | bigint(20) | NO | MUL | NULL | |
| goods_title | varchar(120) | NO | MUL | NULL | |
| goods_price | decimal(10,2) | NO | | NULL | |
| rate_txt | text | NO | MUL | NULL | |
+-------------+---------------+------+-----+---------+-------+
7 rows in set (0.01 sec)
rate_buyer的结构
mysql> DESC rate_buyer;
+-----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+-------+
| uid | bigint(20) | NO | PRI | NULL | |
| sex | tinyint(1) | NO | MUL | 0 | |
| costsum | decimal(12,2) | NO | | 0.00 | |
| costavg | decimal(12,2) | NO | | 0.00 | |
| costmax | decimal(12,2) | NO | | 0.00 | |
| costmin | decimal(12,2) | NO | | 0.00 | |
| costcount | int(10) | NO | | 0 | |
| timefirst | int(10) | NO | | NULL | |
| timelast | int(10) | NO | | NULL | |
| is_seller | tinyint(1) | NO | MUL | 0 | |
| uptime | int(10) | NO | | NULL | |
+-----------+---------------+------+-----+---------+-------+
11 rows in set (0.00 sec)
我想要这个结果，搜索anykeywords列出与tbdb.ratedat不同的uid_sell：
SELECT a.*,b.goods_title,b.goods_price,COUNT(b.trade_id) AS relike
FROM rate_buyer a
INNER JOIN ratedat b ON a.uid=b.uid_buy
WHERE b.goods_title LIKE '%MP3%'
GROUP BY a.uid ORDER BY relike DESC LIMIT 0 , 100 ;
但是tbdb.ratedat的大小超过10GB，当我运行sql时，程序运行超时。
我该怎么做才能得到类似sql的结果？
最佳答案
使用fulltext索引。例如。：
create fulltext index fulltext_goods_title on ratedat (goods_title);
select * from ratedat WHERE MATCH (goods_title) AGAINST ('MP3');