MySQL匹配…对查询非常慢

我在一个运行在Propel ORM上的网站上工作，我有这个查询:

if(isset($args["QueryText"]) && $args["QueryText"] != "") {
  $query = $query
    ->withColumn("(MATCH (Request.Subject, Request.Detail) AGAINST ('" . $args["QueryText"] . "' IN BOOLEAN MODE) + MATCH (Response.Response) AGAINST ('" . $args["QueryText"] . "' IN BOOLEAN MODE))", "RequestRelevance")
    ->condition('cond1', "(MATCH (Request.Subject, Request.Detail) AGAINST ('" . $args["QueryText"] . "' IN BOOLEAN MODE) + MATCH (Response.Response) AGAINST ('" . $args["QueryText"] . "' IN BOOLEAN MODE)) > 0.2")
    ->condition('cond2', 'Request.Id = ?', $args["QueryText"])
    ->where(array('cond1', 'cond2'), 'or')
    ->orderBy("RequestRelevance", Criteria::DESC);
}

在SQL中转换为以下内容:

SELECT DISTINCT 
(MATCH (requests.subject, requests.detail) AGAINST ('46104' IN BOOLEAN MODE) + 
    MATCH (Response.response) AGAINST ('46104' IN BOOLEAN MODE)) 
  AS RequestRelevance, requests.requestID AS "Id", requests.subject AS "Subject", requests.detail AS "Detail", requests.created AS "CreatedDate", requests.lastresponsedate AS "LastResponseDate", SupportStatus.supportstatusID AS "SupportStatus.Id", SupportStatus.supportstatus AS "SupportStatus.Name", SupportStatus.isnew AS "SupportStatus.IsNew", SupportStatus.isclosed AS "SupportStatus.IsClosed", CustomerGroup.customergroupID AS "CustomerGroup.Id", CustomerGroup.customergroup AS "CustomerGroup.Name", Site.siteID AS "Site.Id", Site.site AS "Site.Name", InternalUser.userID AS "InternalUser.Id", InternalUser.username AS "InternalUser.Username", User.userID AS "User.Id", User.username AS "User.Username", Customer.customerID AS "Customer.Id", Customer.customer AS "Customer.Name", Customer.customergroupID AS "Customer.CustomerGroupId", Customer.rate AS "Customer.Rate" 
FROM requests 
  LEFT JOIN responses Response ON (requests.requestID=Response.requestID) 
  INNER JOIN supportstatus SupportStatus ON (requests.supportstatusID=SupportStatus.supportstatusID) 
  INNER JOIN customergroups CustomerGroup ON (requests.customergroupID=CustomerGroup.customergroupID)
  INNER JOIN customers Customer ON (requests.customerID=Customer.customerID) 
  INNER JOIN sites Site ON (requests.siteID=Site.siteID) 
  LEFT JOIN users InternalUser ON (requests.internaluserID=InternalUser.userID) 
  LEFT JOIN users User ON (requests.userID=User.userID)
WHERE ((MATCH (requests.subject, requests.detail) AGAINST ('46104' IN BOOLEAN MODE) + 
  MATCH (Response.response) AGAINST ('46104' IN BOOLEAN MODE)) > 0.2 OR requests.requestID = '46104') 
ORDER BY requests.created ASC,RequestRelevance DESC

使用Propel加载网站需要20秒，运行SQL查询需要7.020秒。

我试了下面这个:

SELECT DISTINCT 
  requests.requestID AS "Id", requests.subject AS "Subject", requests.detail AS "Detail", requests.created AS "CreatedDate", requests.lastresponsedate AS "LastResponseDate", SupportStatus.supportstatusID AS "SupportStatus.Id", SupportStatus.supportstatus AS "SupportStatus.Name", SupportStatus.isnew AS "SupportStatus.IsNew", SupportStatus.isclosed AS "SupportStatus.IsClosed", CustomerGroup.customergroupID AS "CustomerGroup.Id", CustomerGroup.customergroup AS "CustomerGroup.Name", Site.siteID AS "Site.Id", Site.site AS "Site.Name", InternalUser.userID AS "InternalUser.Id", InternalUser.username AS "InternalUser.Username", User.userID AS "User.Id", User.username AS "User.Username", Customer.customerID AS "Customer.Id", Customer.customer AS "Customer.Name", Customer.customergroupID AS "Customer.CustomerGroupId", Customer.rate AS "Customer.Rate" 
FROM requests 
  LEFT JOIN responses Response ON (requests.requestID=Response.requestID) 
  INNER JOIN supportstatus SupportStatus ON (requests.supportstatusID=SupportStatus.supportstatusID) 
  INNER JOIN customergroups CustomerGroup ON (requests.customergroupID=CustomerGroup.customergroupID)
  INNER JOIN customers Customer ON (requests.customerID=Customer.customerID) 
  INNER JOIN sites Site ON (requests.siteID=Site.siteID) 
  LEFT JOIN users InternalUser ON (requests.internaluserID=InternalUser.userID) 
  LEFT JOIN users User ON (requests.userID=User.userID) 
WHERE (requests.subject LIKE '%46104%' OR requests.detail LIKE '%46104%' OR  Response.response LIKE '%46104%' OR requests.requestID = '46104') 
ORDER BY requests.created

需要3.308秒来执行。将OR Response.response LIKE '%46104%'移出后，时间缩短为0.140秒。响应表包含288317行和响应。responses列是一个具有FULLTEXT索引的TEXT()列。

减少这个搜索的执行时间的最好方法是什么?我试过使用这个https://dba.stackexchange.com/questions/15214/why-is-like-more-than-4x-faster-than-match-against-on-a-fulltext-index-in-mysq答案，但是当我执行:

SELECT responseID FROM
(
 SELECT * FROM responses
 WHERE requestID = 15000
 AND responseID != 84056
) A
WHERE MATCH(response) AGAINST('twisted');

我得到这个错误:

Error Code: 1191. Can't find FULLTEXT index matching the column list

帮助将非常感激!

编辑1:

尝试@Richard EB的查询:

ALTER TABLE responses ADD FULLTEXT(response)
288317 row(s) affected Records: 288317  Duplicates: 0  Warnings: 0  78.967 sec

SELECT responseID FROM (     SELECT * FROM responses     WHERE requestID = 15000     AND responseID != 84056 ) A WHERE MATCH(response) AGAINST('twisted') LIMIT 0, 2000 
Error Code: 1191. Can't find FULLTEXT index matching the column list    0.000 sec

删除DISTINCT将执行时间减少到0.952秒，但它不会检索我需要的结果。

编辑2:

执行此查询:

SELECT DISTINCT 
responses.requestID AS "Id" FROM responses WHERE MATCH(response) AGAINST('twisted')

用时0.062秒。

SELECT DISTINCT responses.requestID
  FROM responses 
WHERE (
  MATCH (Responses.response) AGAINST ('twisted' IN BOOLEAN MODE)
) 
ORDER BY responses.requestID ASC

只需要0.046秒。但是，从请求中进行选择并连接响应会减慢查询速度。我不确定这是否意味着整个查询必须完全重写以从响应和加入请求中进行选择?

编辑3:

以下是我在Requests和Responses表上的索引:

    # Table, Non_unique, Key_name, Seq_in_index, Column_name, Collation, Cardinality, Sub_part, Packed, Null, Index_type, Comment, Index_comment
    responses, 0, PRIMARY, 1, responseID, A, 288317, , , , BTREE, , 
    responses, 1, requestID, 1, requestID, A, 48052, , , YES, BTREE, , 
    responses, 1, response, 1, response, , 1, , , YES, FULLTEXT, , 
    responses, 1, response_2, 1, response, , 1, , , YES, FULLTEXT, , 
    responses, 1, response_3, 1, response, , 1, , , YES, FULLTEXT, , 
    # Table, Non_unique, Key_name, Seq_in_index, Column_name, Collation, Cardinality, Sub_part, Packed, Null, Index_type, Comment, Index_comment
    requests, 0, PRIMARY, 1, requestID, A, 46205, , , , BTREE, , 
    requests, 1, supportstatusID, 1, supportstatusID, A, 14, , , YES, BTREE, , 
    requests, 1, internaluserID, 1, internaluserID, A, 344, , , YES, BTREE, , 
    requests, 1, customergroupID, 1, customergroupID, A, 198, , , , BTREE, , 
    requests, 1, userID, 1, userID, A, 1848, , , YES, BTREE, , 
    requests, 1, siteID, 1, siteID, A, 381, , , YES, BTREE, , 
    requests, 1, request, 1, subject, , 1, , , YES, FULLTEXT, , 
    requests, 1, request, 2, detail, , 1, , , YES, FULLTEXT, , 
    requests, 1, request, 3, ponumber, , 1, , , YES, FULLTEXT, ,