Mysql Query Optimization [Match Against] I have tried all the possible option to optimize the query, but no luck.
Query 1: 

SELECT 
  *, 
  (
    3959 * acos (
      cos(
        radians(51.5073509)
      ) * cos(
        radians(ats_latitude)
      ) * cos(
        radians(ats_longitude) - radians(-0.12775829999998223)
      ) + sin(
        radians(51.5073509)
      ) * sin(
        radians(ats_latitude)
      )
    )
  ) AS `distance`, 
  (
    SELECT 
      `distance`
  ) &lt; 75 AS `in_user_radius` 
FROM 
  table_name 
WHERE 
  `feed` = 'external' 
  AND (
    MATCH (`keywords`) AGAINST (
      'word1 word2 word3*' IN BOOLEAN MODE
    ) 
    OR `title` LIKE '%word1 word2 word3%'
  ) 
  AND `business_area` RLIKE ('word1') 
  AND 1 
HAVING 
  (
    `distance` &lt; 95 
    OR isnull(`distance`)
  ) 
  AND (
    `in_user_radius` = '1' 
    OR isnull(`in_user_radius`)
  )


Query 2: 

SELECT
    *,
    MATCH(`keywords`) AGAINST('word1*' IN BOOLEAN MODE) AS `score1`,
    MATCH(`title`) AGAINST('"word2"' IN NATURAL LANGUAGE MODE) AS `score_title_exact`,
    MATCH(`title`) AGAINST('word2' IN NATURAL LANGUAGE MODE) AS `score_title_phrase`
FROM
    table_name
WHERE
    `jobfeed` = 'external' AND(
        MATCH(`keywords`) AGAINST('word1*' IN BOOLEAN MODE) OR `title` LIKE '%word1%'
    )
ORDER BY
    `nationwide`
DESC
    ,
    `score_title_exact`
DESC
    ,
    `score_title_phrase`
DESC
    ,
    `score1`
DESC
    ,
    `id`
DESC
LIMIT 48, 12


Below things I tried to optimize:
1) instead of select * I have specified the specific columns.

Observation: Due MATCH AGAINST condition query is taking too much time to execute.

please suggest some solution to optimize the above queries.
MATCH is not the primary villain in the sluggishness; these are:


OR
Using LIKE / RLIKE
pagination via OFFSET
Most of the above prevent the use of any INDEX, hence the query must scan the entire table, thereby being slow for lots of reasons.


Some can be solved in SQL; most require changes to the specifications for the query.

Since the first thing the Optimizer will use is a FULLTEXT clause, I will focus on them.

        MATCH(`keywords`) AGAINST('word1*' IN BOOLEAN MODE)
     OR `title` LIKE '%word1%'


This can be simplified (approximately) by:

FULLTEXT(keywords, title)   -- add this

MATCH(`keywords`, `title`) AGAINST('word1*' IN BOOLEAN MODE)


That should make query2 much faster.

Query1 is pretty hopeless as a starting point.  Can we start with query2 instead?

SELECT ...
    FROM ( ...query2... ) AS x
    JOIN table_name AS t2  USING(id)
    WHERE ...
    HAVING ...
    ORDER BY ...  LIMIT ...   -- moved from query2


The idea is to


combine the tests as discussed above;
do the most filtering possible with the least work in the derived table (the subquery);
join back to the same table to pick up things left out of the subquery;
finish with the rest of the work.