title: MySQL Query does not use index in table join
body: <p>I am trying to list all the book_sales information for a particular book author. So I have a query and it's not using Index to lookup records. </p>
  
  <p><strong>The following is my tables structure:</strong></p>
  
  <pre><code>-- Table structure for table `books`
  
  CREATE TABLE IF NOT EXISTS `books` (
    `book_id` int(11) NOT NULL auto_increment,
    `author_id` int(11) unsigned NOT NULL,
    `book_type_id` int(11) NOT NULL,
    `book_title` varchar(50) NOT NULL,
    `book_price` smallint(4) NOT NULL,
    `in_stock` char(1) NOT NULL,
    PRIMARY KEY  (`book_id`),
    KEY `book_type_id` (`book_type_id`),
    KEY `author_id` (`author_id`)
  ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
  
  -- Dumping data for table `books`
  
  INSERT INTO `books` (`book_id`, `author_id`, `book_type_id`, `book_title`, `book_price`, `in_stock`) VALUES
  (1, 1, 1, 'My Book 1', 10, 'y'),
  (2, 2, 1, 'My Book 2', 20, 'n'),
  (3, 1, 2, 'My Book 3', 30, 'y'),
  (4, 3, 3, 'My Book 4', 40, 'y'),
  (5, 4, 2, 'My Book 5', 50, 'n'),
  (6, 1, 1, 'My Book 6', 60, 'y'),
  (7, 5, 3, 'My Book 7', 70, 'n'),
  (8, 6, 2, 'My Book 8', 80, 'n'),
  (9, 7, 1, 'My Book 9', 90, 'y'),
  (10, 8, 3, 'My Book 10', 100, 'n');
  
  -- Table structure for table `book_sales`
  
  CREATE TABLE IF NOT EXISTS `book_sales` (
    `sale_id` int(11) NOT NULL auto_increment,
    `book_id` int(11) NOT NULL,
    `sale_amount` decimal(8,2) NOT NULL default '0.00',
    `time` datetime NOT NULL default '0000-00-00 00:00:00',
    `price` smallint(8) NOT NULL,
    PRIMARY KEY  (`sale_id`),
    KEY `book_id` (`book_id`),
    KEY `price` (`price`)
  ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
  
  -- Dumping data for table `book_sales`
  
  INSERT INTO `book_sales` (`sale_id`, `book_id`, `sale_amount`, `time`, `price`) VALUES
  (1, 1, '10.00', '2010-02-23 10:00:00', 20),
  (2, 1, '20.00', '2010-02-24 11:00:00', 20);
  </code></pre>
  
  <p><strong>My Query:</strong></p>
  
  <pre><code>SELECT sale_amount, price
  FROM book_sales
  INNER JOIN books ON book_sales.book_id = books.book_id
  WHERE books.author_id =1
  </code></pre>
  
  <p><strong>An EXPLAIN on the above, shows me:</strong></p>
  
  <pre><code>id   select_type    table        type        possible_keys      key      key_len    ref     rows      Extra
  1   SIMPLE          books       ref     PRIMARY,author_id   author_id   4         const    3        Using index
  1   SIMPLE          book_sales  ALL     book_id             NULL        NULL      NULL     2        Using where
  </code></pre>
  
  <p>Clearly, book_sales is not using the key 'book_id', although I have it. What can I do to make the book_sales table use the Index? </p>
  
  <p>Thank you.</p>
  
  <p><strong>Edits done based on suggestions (but the result is they still do not use index):</strong></p>
  
  <pre><code>//Does not use the index in book_sales table
  EXPLAIN SELECT sale_amount, price
  FROM books, book_sales
  FORCE INDEX ( book_id ) 
  WHERE book_sales.book_id = books.book_id
  AND books.author_id =1
  
  //Does not use the index in book_sales table
  EXPLAIN SELECT sale_amount, price
  FROM book_sales, books
  WHERE books.author_id = 1
  AND book_sales.book_id = books.book_id
  </code></pre>
  
  <p>How to force the book_sale table with just 2 rows, to use the index ? Thank  you.</p>