title: Optimize three tables joins in MySQL
body: <p>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.</p>
  
  <ol>
  <li><code>book_categories</code> (cat_id, parent_id DEFAULT NULL, cat_name, parent_cat_name DEFAULT NULL)</li>
  <li><code>authors</code> (author_id, author_name, active)</li>
  <li><code>books</code> (book_id, cat_id, author_id, book_title, book_contents, book_status ENUM (published,pending,deleted) publish_date)</li>
  </ol>
  
  <p>Here is SQL code</p>
  
  <pre><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
  </code></pre>
  
  <p>EXPLAIN of the above query</p>
  
  <pre><code>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    
  </code></pre>
  
  <h2>Indexes &amp; Keys</h2>
  
  <pre><code>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_date);
  </code></pre>