sql - MySql:让这个查询更快……有办法吗？第二部分

Question

这是问题的第二部分:MySql: make this query faster... is there a way?

此查询仍然运行缓慢:

SELECT b.id,
       b.name,
       c.name
FROM bookcorr as a JOIN books as b on b.id = a.books_id =
JOIN Library as c on c.id = a.library_id 
WHERE a.category_id = '2521' 
AND a.library_id = '4983' 
ORDER BY b.name ASC LIMIT 0,15

有什么建议吗？

CREATE TABLE `bookcorr` (
  `category_id` smallint(4) unsigned NOT NULL,
  `book_id` mediumint(7) unsigned NOT NULL,
  `library_id` smallint(5) unsigned NOT NULL,
  UNIQUE KEY `cat_id_3` (`category_id`,`book_id`,`library_id`),
  KEY `category_id` (`category_id`),
  KEY `book_id` (`book_id`),
  KEY `library_id` (`library_id`),
  KEY `cat_id_2` (`cat_id`,`com_id`))

CREATE TABLE `books` (
  `id` mediumint(7) unsigned NOT NULL AUTO_INCREMENT,
  `com_id` smallint(5) unsigned NOT NULL,
  `name` varchar(256) NOT NULL,
    ....
  PRIMARY KEY (`id`),
  KEY `name` (`name`),
  FULLTEXT KEY `search` (`name`,`author`)
)

 CREATE TABLE `Library` (
  `id` smallint(4) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(256) NOT NULL,
  ...
  UNIQUE KEY `id` (`id`),
  KEY `library_name` (`name`)
) 

CREATE TABLE `Category` (
  `id` smallint(4) unsigned NOT NULL,
  `name` varchar(100) NOT NULL,
  UNIQUE KEY `id` (`id`),
  KEY `name` (`name`),
)

这里:

+----+-------------+-------+--------+----------------------------------------+----------+---------+----------------------------+------+-------------+
| id | select_type | table | type   | possible_keys                          | key      | key_len | ref                        | rows | Extra       |
+----+-------------+-------+--------+----------------------------------------+----------+---------+----------------------------+------+-------------+
|  1 | SIMPLE      | b     | index  | NULL                                   | name     | 258     | NULL                       |   15 |             |
|  1 | SIMPLE      | a     | eq_ref | cat_id_3,cat_id,book_id,library_id     | cat_id_3 | 7       | const,b.id,const           |    1 | Using index |
|  1 | SIMPLE      | c     | const  | PRIMARY                                | PRIMARY  | 2       | const                      |    1 | Using where |
+----+-------------+-------+--------+----------------------------------------+----------+---------+----------------------------+------+-------------+

Answer 1

看来您需要研究如何使用“explain select”。

这将使您的所有查询更快! :)