I need your help to optimize the query to avoid using "Using filesort".The job of the query is to select all the articles that belongs to specific tag. The query is:
select title
from tag,
article
where tag = 'Riyad'
AND tag.article_id = article.id
order by tag.article_id
The tables structure are the following:
Tag table
CREATE TABLE `tag` (
`tag` VARCHAR( 30 ) NOT NULL ,
`article_id` INT NOT NULL ,
KEY `tag` (`tag`),
KEY `article_id` (`article_id`)
) ENGINE = MYISAM ;
Article table
CREATE TABLE `article` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`title` VARCHAR( 60 ) NOT NULL
) ENGINE = MYISAM
Sample data
INSERT INTO `article` VALUES (1, 'About Riyad');
INSERT INTO `article` VALUES (2, 'About Newyork');
INSERT INTO `article` VALUES (3, 'About Paris');
INSERT INTO `article` VALUES (4, 'About London');
INSERT INTO `tag` VALUES ('Riyad', 1);
INSERT INTO `tag` VALUES ('Saudia', 1);
INSERT INTO `tag` VALUES ('Newyork', 2);
INSERT INTO `tag` VALUES ('USA', 2);
INSERT INTO `tag` VALUES ('Paris', 3);
INSERT INTO `tag` VALUES ('France', 3);
In table tag
, replace the key on tag
with a key on (tag
, article_id
):
ALTER TABLE `tag` DROP INDEX `tag`, ADD INDEX `tag_article_id` (`tag`, `article_id`)
MySQL will only use one index on a table to perform a query. At present, it is using the index on tag
, but cannot use the other index to perform the ORDER BY. If you put the second column in the index then it will use it to help with the ORDER BY.