Monday, 3 September 2018

MySQL does not use the index in a properly indexed SELECT and hellip; UNION

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


) 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.

0 comments:

Post a Comment