MySql:让这个查询更快......有办法吗?第二部分 [英] MySql: make this query faster… is there a way ? PART TWO
本文介绍了MySql:让这个查询更快......有办法吗?第二部分的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
这是问题的第二部分:MySql:进行此查询更快……有办法吗?
此查询仍然运行缓慢:
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 |
+----+-------------+-------+--------+----------------------------------------+----------+---------+----------------------------+------+-------------+
推荐答案
看来你需要学习如何使用explain select"了.
Looks like you need to study how to use "explain select".
它将使您的所有查询更快!:)
It will make all your queries faster! :)
这篇关于MySql:让这个查询更快......有办法吗?第二部分的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文