title: MySQL is not using index in a properly indexed SELECT ... UNION
body: <p>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 <a href="https://stackoverflow.com/questions/38495389/optimizing-a-select-union-query-with-order-and-limit-on-a-table-with-5m">Optimizing a SELECT … UNION … query with ORDER and LIMIT on a table with 5M+ rows</a> besides having all the indexes defined, the query is still logged as "not using index".</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>I don't know how should I use an index in the outer SELECT where it's grouping the two SELECTs using UNION.</p>
  
  <p>Thanks</p>
  
  <p><strong>Update</strong></p>
  
  <p>This is the structure of the <code>article</code> table:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p><strong>UPDATE 2:</strong></p>
  
  <p>Here is the output of <code>EXPLAIN SELECT</code> (I didn't know how to get it from phpMyAdmin any better! Sorry if it doesn't look good):</p>
  
  <pre><code>id  select_type table   type    possible_keys   key key_len ref rows    Extra
  1   PRIMARY &lt;derived2&gt;  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    &lt;union2,3&gt;  ALL NULL    NULL    NULL    NULL    NULL    Using temporary
  </code></pre>