Php & mySQL: query does not use index in table join

Question

I am trying to list all the book_sales information for a particular author of a book. Therefore, I have a query, and it does not use Index to search for records.

Below is my table structure:

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

My request:

 SELECT sale_amount, price FROM book_sales INNER JOIN books ON book_sales.book_id = books.book_id WHERE books.author_id =1 

The EXPLANATION above shows me:

 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 

Obviously, book_sales does not use the "book_id" key, although I have one. What can I do to make the book_sales table use an index?

Thanks.

Editing is based on sentences (but as a result, they still do not use the index):

 //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 

How to force a book_sale table with two rows to use an index? Thanks.

Answer 1

As you can see in EXPLAIN, "book_id" is displayed as a possible key. If MySQL does not use it, the optimizer simply does not believe that this will speed up the query. This is true if book_sales has only 2 lines, and 100% of these lines have the same book_id. He called power in relation to the battery. Avoiding Table Scans (MySQL Guide)

Try filling it with a lot of rows and you will see that MySQL will use the index to connect.

Edit: request

 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 

... will not work in this case, because the optimizer still recognizes that reading the index is suboptimal and switches the order of the tables so as not to. You can force the order of tables to use STRAIGHT_JOIN . This, however, is a bit of a hack because it forces MySQL to execute the query in a way that is not the best.

  EXPLAIN SELECT sale_amount, price FROM books STRAIGHT_JOIN book_sales FORCE INDEX (book_id) ON book_sales.book_id = books.book_id WHERE books.author_id = 1 

Answer 2

try it

 SELECT sale_amount, price FROM book_sales,books LEFT JOIN books ON(book_sales.book_id = books.book_id) WHERE books.author_id =1