Optimizing MySql query to avoid using "Using filesort"

Question

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

Answer 1

You're joining, in part, on tag.article_id=article.id, but don't have an index in place on the tag side to support that optimally. Add a secondary (non-unique) index on tag.article_id.

Answer 2

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.