MySQL is not using index in a properly indexed SELECT ... UNION

Question

I've read multiple questions in here but none could help me so far. For the same query and table structure on my previous [unanswered] question Optimizing a SELECT … UNION … query with ORDER and LIMIT on a table with 5M+ rows besides having all the indexes defined, the query is still logged as "not using index".

SELECT  `id`, `title`, `title_fa`
    FROM  
      ( SELECT  `p`.`id` AS `id`, `p`.`title` AS `title`, `p`.`title_fa` AS `title_fa`,
                `p`.`unique` AS `unique`, `p`.`date` AS `date`
            FROM  `articles` `p`
            LEFT JOIN  `authors` `a`  ON `p`.`unique` = `a`.`unique`
            WHERE  1
              AND  MATCH (`p`.`title`) AGAINST ('"heat"' IN BOOLEAN MODE)
            UNION 
         SELECT  `p`.`id` AS `id`, `p`.`title` AS `title`, `p`.`title_fa` AS `title_fa`,
                `p`.`unique` AS `unique`, `p`.`date` AS `date`
            FROM  `articles` `p`
            LEFT JOIN  `authors` `a`  ON `p`.`unique` = `a`.`unique`
            WHERE  1
              AND  MATCH (`p`.`title_fa`) AGAINST ('"گرما"' IN BOOLEAN MODE)
      ) AS `subQuery`
    GROUP BY  `unique`
    ORDER BY  `date` DESC
    LIMIT  0,10;

I don't know how should I use an index in the outer SELECT where it's grouping the two SELECTs using UNION.

Thanks

Update

This is the structure of the article table:

CREATE TABLE `articles` (
  `id` int(10) unsigned NOT NULL,
  `title` text COLLATE utf8_persian_ci NOT NULL,
  `title_fa` text COLLATE utf8_persian_ci NOT NULL,
  `description` text COLLATE utf8_persian_ci NOT NULL,
  `description_fa` text COLLATE utf8_persian_ci NOT NULL,
  `date` date NOT NULL,
  `unique` tinytext COLLATE utf8_persian_ci NOT NULL,
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_persian_ci;

ALTER TABLE `articles`
  ADD PRIMARY KEY (`id`),
  ADD KEY `unique` (`unique`(128)),
  ADD FULLTEXT KEY `TtlDesc` (`title`,`description`);
  ADD FULLTEXT KEY `Title` (`title`);
  ADD FULLTEXT KEY `faTtlDesc` (`title_fa`,`description_fa`);
  ADD FULLTEXT KEY `faTitle` (`title_fa`);
  MODIFY `id` int(10) unsigned NOT NULL AUTO_INCREMENT;

UPDATE 2:

Here is the output of EXPLAIN SELECT (I didn't know how to get it from phpMyAdmin any better! Sorry if it doesn't look good):

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   PRIMARY <derived2>  ALL NULL    NULL    NULL    NULL    4   Using temporary; Using filesort
2   DERIVED p   fulltext    title   title   0   NULL    1   Using where
3   UNION   p   fulltext    title_fa    title_fa    0   NULL    1   Using where
NULL    UNION RESULT    <union2,3>  ALL NULL    NULL    NULL    NULL    NULL    Using temporary

Answer 1

) ASsubQuery

It is a subquery, a derived table, and it is manifested coming out of a temporary table. It has no chance of index use.

As I wrote in this answer:

The document Derived Tables in MySQL 5.7 describes it well for versions 5.6 and 5.7, where the latter will provide no penalty due to the change in materialized derived table output being incorporated into the outer query. In prior versions, substantial overhead was endured with temporary tables with the derived.

Answer 2

When there is a MATCH clause, only a FULLTEXT index will be used.

Meanwhile, tips on syntax and pagination:

The usual pattern:

( SELECT ...
    GROUP BY ... ORDER BY ... -- apply to result of inner SELECT
)
UNION ALL
( SELECT ...
    GROUP BY ... ORDER BY ... -- apply to result of inner SELECT
)
GROUP BY ... ORDER BY ... -- apply to result of UNION

(If you need pagination, see my blog .)

Addenda

In the EXPLAIN... The 1st and 4th lines say ALL and NULL -- this indicates that no index was used in any way. In those cases, we are talking about 4 rows, and all 4 rows are needed. So, do not worry that no INDEX was used.

In the 2nd and 3rd lines, a FULLTEXT index was used.

The phrase Using index (which does not show in your EXPLAIN) does not mean "using some index", it means "using only the index". To elaborate... The data for a table is in one place, the index is in another. When all the necessary columns are in the index, the query does not need to reach over into the data. This is labeled as Using index, and it is termed a "covering index". This particular situation is not relevant for your query.

A similar phrase, Using index condition, means something else. It says that the WHERE clause can be handled by the storage engine, and does not need to involve the handler. Let's simply say that it is an optimization making things run a little faster.

Bottom line: You query is well written, and your indexes are fine for this query.

Maybe no UNION?

Try getting rid of the UNION and simply search for both strings at the same time:

FULLTEXT(title, title_fa)
MATCH (title, title_fa) AGAINST ('"heat" "گرما"' IN BOOLEAN MODE)

If that does not work, then explain what goes wrong.