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
) AS
subQuery
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