Speed improvements of MySQL MATCH query-CodePudding

Trying to see if there is a way I can speed up a query I have built for a site I have been working on that utilized "MATCH"

$r=$this->query("SELECT product_id,`locale`,
       MATCH (description) AGAINST ('".$keyword."') as matches,
       description
   FROM search_index
   HAVING matches>0
      AND locale='".$locale."'
   ORDER BY matches DESC limit ".$limit);

Basically, I am querying a table with a small number of columns with about 6000 records. But it taking a few seconds each time - and hoping I can dial that down a bit.

Here is the table structure if it helps...

Not sure if I have the index set up properly, or perhaps changing table type? Or perhaps querying and getting results that fit the locale condition before I query for matches (or perhaps it is doing that part already...)

Thanks in advance for any insight.

---- update -----

Some more information based off the feedback from Rick

I tried modifying based on your feedback. Here is an original sql

SELECT product_id,locale, MATCH (description) AGAINST ('saw blade') as matches, description FROM search_index HAVING matches>0 AND locale='en_us' ORDER BY matches DESC

I modified based off your feedback (though - probably incorrectly!)

SELECT product_id,locale, MATCH (description) AGAINST ('saw blade') as matches, description FROM search_index WHERE MATCH (description) AGAINST ('saw blade') AND locale='en_us' ORDER BY matches DESC

They both take about the same time to execute, unfortunately (about 6-7 seconds).

When I try an EXPLAIN on the latter query - I get this

CodePudding user response：

Change "HAVING" to "WHERE" and repeat the MATCH in the WHERE.

HAVING is not done until later; you need it to happen at WHERE time.

For further discussion, please provide EXPLAIN SELECT ...

CodePudding user response：

Ended up speeding this up significantly by switching the description field from varchar to text. Night and day difference. Not sure why... - but I will take it!