Optimizing MySql query to avoid using "Using filesort"
- by usef_ksa
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 ,
INDEX ( `tag` )
) 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);
