#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2010
    Posts
    36
    Rep Power
    10

    Question Query does not use index in table join


    Hi,
    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.

    The following is my tables structure:

    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);
    My Query:
    PHP Code:
    SELECT sale_amountprice
    FROM book_sales
    INNER JOIN books ON book_sales
    .book_id books.book_id
    WHERE books
    .author_id =
    An EXPLAIN on the above, shows me:

    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
    Clearly, book_sales is not using the key 'book_id', although I have it. What can do make the book_sales table use the Index? Thank you.
  2. #2
  3. No Profile Picture
    Problem Solver
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jan 2001
    Location
    Stockholm, Sweden
    Posts
    4,480
    Rep Power
    539
    Originally Posted by devner
    Clearly, book_sales is not using the key 'book_id', although I have it. What can do make the book_sales table use the Index? Thank you.
    You seem to have very few rows in the table and that is probably the problem.
    My guess is that it isn't using the index because you have so few rows that it is faster to just scan the table instead of using the index.

    Comments on this post

    • devner agrees : Makes sense. Thank you.
    /Stefan
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2006
    Posts
    331
    Rep Power
    46
    A covering index on table `book_sales` of the fields `book_id`, `sale_amount`, and `price` would mean that everything needed in the query could be retrieved from the index, and hence no lookups would even be necessary.

    If you created it in the order I listed above, then you could delete the current index you have on `book_id` as it would then become redundant (due to leftmost index prefix).
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2010
    Posts
    36
    Rep Power
    10
    @ccalender
    I shall try that and see how that works. Thank you.
  8. #5
  9. Modding: Oracle MsSQL Firebird
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2001
    Location
    Outside US
    Posts
    8,517
    Rep Power
    543
    Originally Posted by sr
    You seem to have very few rows in the table and that is probably the problem.
    ...
    And the fact that 100% of the table rows are matched so the index isn't more selective that a full table scan, that would anyway be needed to retrieve the desired attributes

    Comments on this post

    • sr agrees : Good point!
    • devner agrees : Nice one!

IMN logo majestic logo threadwatch logo seochat tools logo