Я пытаюсь перечислить всю book_sales информацию для конкретного книжного автора. Таким образом, у меня есть запрос, и он не использует Индекс для записей поиска.
Следующее является моей структурой таблиц:
-- 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);
Мой запрос:
SELECT sale_amount, price
FROM book_sales
INNER JOIN books ON book_sales.book_id = books.book_id
WHERE books.author_id =1
ОБЪЯСНЕНИЕ на вышеупомянутом, шоу меня:
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
Очевидно, book_sales не использует ключ 'book_id', хотя у меня есть он. Что я могу сделать, чтобы заставить book_sales таблицу использовать Индекс?
Спасибо.
Редактирования, сделанные на основе предложений (но результат они все еще, не используют индекс):
//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
Как вызвать book_sale таблицу со всего 2 строками, для использования индекса? Спасибо.
Как вы можете видеть в EXPLAIN, "book_id" указан как возможный ключ. Если MySQL не использует его, оптимизатор просто не думает, что это ускорит запрос. Что верно, если "book_sales" имеет только 2 строки, и 100% этих строк имеют один и тот же "book_id". Кстати, это называется кардинальностью. Как избежать сканирования таблицы (Руководство по MySQL)
Попробуйте заполнить ее большим количеством строк, и вы увидите, что MySQL будет использовать индекс для соединения.
Изменить: запрос
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
... в этом случае тоже не будет работать, потому что оптимизатор по-прежнему распознает, что чтение индекса является неоптимальным, и переключает порядок таблицы, чтобы избежать этого. Вы можете принудительно упорядочить таблицу с помощью STRAIGHT_JOIN . Это, однако, своего рода взлом, потому что он заставляет MySQL выполнять запрос не лучшим образом.
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
Попробовать
SELECT sale_amount, price
FROM book_sales,books
LEFT JOIN books ON(book_sales.book_id = books.book_id)
WHERE books.author_id =1