Geek Answers Handbook

MySQL Match ... Against the query is very slow

I am working on a site running on Propel ORM and I have this request:

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); }

which translates to 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.twistedfish_userID=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

It takes 20 seconds to download to the site using Propel and 7.020 seconds when executing an SQL query.

Instead, I tried the following:

 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.twistedfish_userID=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

which takes 3.308 seconds to complete. Removing OR Response.response LIKE '%46104%' from it reduces the time to 0.140 seconds. The response table contains 288317 rows, and the response.responses column contains the TEXT () column with the FULLTEXT index.

What would be the best way to shorten the execution time of this search? I tried using this https://dba.stackexchange.com/questions/15214/why-is-like-more-than-4x-faster-than-match-against-on-a-fulltext-index-in-mysq answer however, when I execute:

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

I get this error:

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

Help will be greatly appreciated!

EDIT 1:

@Richard EB trial request:

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

But:

 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

Removing DISTINCT reduces the execution time to 0.952 seconds, however it does not return the results I need.

EDIT 2:

Running this request:

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

takes 0.062 seconds

Doing this:

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

takes only 0.046 sec. However, selecting from queries and attaching to responses is what slows down a query. I'm not sure if this means that the entire request must be completely rewritten to select from the answers and join the requests?

EDIT 3:

Here are the indexes I have in the Requests and Responses tables:

  # 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, twistedfish_userID, 1, twistedfish_userID, 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, ,
+6
source share
1 answer

A LIKE search will go through each entry and perform inaccurate string comparisons, so it is so slow.

The mysql error you inserted indicates that the column referenced in the MATCH clause does not have a full-text index (or it does, but not as it is mentioned in the MATCH clause).

Assuming you are using MyISAM or MySQL 5.6 and InnoDB, try:

 ALTER TABLE responses ADD FULLTEXT(response);

Edit: answer (in comments): “If there are two columns in a MATCH statement, then for both of these columns there must be one full-text index, not two full-text indexes for each column”

+3
source

More articles:


All Articles