Optimize three tables joins in MySQL

I am building an e-library, I have written a query which scans the rows more rows probably due to poor indexing. Here is my table structure.

1. book_categories (cat_id, parent_id DEFAULT NULL, cat_name, parent_cat_name DEFAULT NULL)
2. authors (author_id, author_name, active)
3. books (book_id, cat_id, author_id, book_title, book_contents, book_status ENUM (published,pending,deleted) publish_date)

Here is SQL code

  SELECT T.author_id, 
         T.author_name, 
         C.parent_id, 
         C.cat_id, 
         C.cat_name, 
         B.book_id, 
         B.book_title, 
         B.book_contents, 
         B.publish_date 
    FROM books B, 
         authors T, 
         book_categories C 
   WHERE B.author_id = T.author_id 
     AND C.cat_id = B.cat_id 
     AND book_status = 'published' 
ORDER BY published_date DESC

EXPLAIN of the above query

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
-----------------------------------------------------------------------------------------------------
1   SIMPLE  C   ALL     PRIMARY     NULL    NULL    NULL    449     Using where; Using temporary; Using filesort
1   SIMPLE  B   ref     author_id,cat_id    cat_id  4   bookdb.C.cat_id     214     Using where
1   SIMPLE  T   eq_ref  PRIMARY     PRIMARY     4   bookdb.B.author_id  1    

Indexes & Keys

Alter table books 
add Foreign Key (author_id) references authors (author_id) on delete  restrict on update  restrict;

Alter table books 
add Foreign Key (cat_id) references book_categories (cat_id) on delete  restrict on update  restrict;

Create Index books_INX ON books(cat_id,book_status,published_dat开发者_StackOverflowe);

Your indexes are fine. Don't worry about it scanning a few hundred rows (~0.5% of the table), as long as it is being done quickly. Is it quick enough?

I don't see any indexes created on those tables... if there aren't indexes don't expect MySQL to create them on "the fly" and use them. How about defining some?

http://dev.mysql.com/doc/refman/5.0/en/create-index.html

Try adding author_id to index books_INX. Try to move published_date to different place (first, last...) in books_INX. For example:

Create Index books_INX ON books(cat_id,author_id,book_status,published_date);

Make sure that author_id and cat_id are primary keys for author and book_category tables, respectively. Also book_id should probably be the primary key for books table, even if it shouldn't affect this particular query.