MySQL Query Optimization -

i have web application use similar table scheme below. want optimize selection of articles. articles selected based on tag given. example, if tag 'iphone' , query should output open articles 'iphone' last month.

create table `article` ( `id` int(11) not null auto_increment, `title` varchar(100) not null, `body` varchar(200) not null, `date` timestamp not null default current_timestamp, `author_id` int(11) not null, `section` varchar(30) not null, `status` int(1) not null, primary key  (`id`) ) engine=myisam  default charset=utf8 auto_increment=1 ; 

create table `tags` ( `name` varchar(30) not null, `article_id` int(11) not null, primary key  (`name`,`article_id`) ) engine=myisam  default charset=utf8; 

create table `users` ( `id` int(11) not null auto_increment, `username` varchar(30) not null, primary key  (`id`) ) engine=myisam  default charset=utf8 auto_increment=3 ; 

the following mysql query

 explain select article.id,users.username,article.title   article,users,tags   article.id=tags.article_id , tags.name = 'iphone4'   , article.author_id=users.id , article.status = '1'   , article.section = 'mobile'   , article.date > '2010-02-07 13:25:46'   order tags.article_id desc  

output

id  select_type     table   type    possible_keys   key     key_len     ref     rows    <br> 1   simple  tags    ref     primary     primary     92  const   55  using where; using index <br> 1   simple  article     eq_ref  primary     primary     4   test.tags.article_id    1   using <br> 1   simple  users   eq_ref  primary     primary     4   test.article.author_id  1     <br> 

possible optimize more?

this query may optimized, depending on condition more selective: tags.name = 'iphone4' or article.date > '2010-02-07 13:25:46'

if there less articles tagged iphone posted after feb 7, original query nice.

if there many articles tagged iphone, few posted after feb 7, query more efficient:

select  article.id, users.username, article.title    tags join    article on      article.id = tags.article_id         , article.status = '1'         , article.section = 'mobile'         , article.date > '2010-02-07 13:25:46' join    users on      users.id = article.author_id   tags.name = 'iphone4' order         tags.article_date desc, tags.article_id desc 

note order by condition has changed. may or may not want, however, orders of id , date correspond each other.

if need original order by condition may leave add filesort (or revert original plan).

in either case, create index on

article (status, section, date, id)