MySQL慢查询

Question

SELECT
  items.item_id, items.category_id, items.title, items.description, items.quality,
  items.type, items.status, items.price, items.posted, items.modified,
  zip_code.state_prefix, zip_code.city, books.isbn13, books.isbn10, books.authors,
  books.publisher
FROM
(
  (
    items
    LEFT JOIN bookitems ON items.item_id = bookitems.item_id
  )
  LEFT JOIN books ON books.isbn13 = bookitems.isbn13
)
LEFT JOIN zip_code ON zip_code.zip_code = items.item_zip
WHERE items.rid = $rid`

我正在运行这个查询，以获取用户项的列表及其位置。zip_code表有超过40k条记录，这可能是问题所在。它目前需要15秒来返回一个大约20个项目的列表！我能做些什么来提高这个查询的效率？

更新:下面是相关表的表创建代码。很抱歉格式化！

CREATE TABLE `bookitems` (  
    `bookitem_id` int(10) unsigned NOT NULL auto_increment COMMENT 'BookItem ID',  
    `item_id` int(10) unsigned NOT NULL default '0' COMMENT 'Item ID',  
    `isbn13` varchar(13) NOT NULL default '' COMMENT 'Book ISBN13',  
    `modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT 'Date of Last Modification',  
    PRIMARY KEY  (`bookitem_id`),  
    UNIQUE KEY `item_id` (`item_id`),  
    KEY `fk_bookitems_isbn13` (`isbn13`),  
    CONSTRAINT `fk_bookitems_isbn13` FOREIGN KEY (`isbn13`) REFERENCES `books` (`isbn13`),  
    CONSTRAINT `fk_bookitems_item_id` FOREIGN KEY (`item_id`) REFERENCES `items` (`item_id`)  
) ENGINE=InnoDB AUTO_INCREMENT=82 DEFAULT CHARSET=latin1;  

CREATE TABLE `books` (  
  `isbn13` varchar(13) NOT NULL default '' COMMENT 'Book ISBN13 (pk)',  
  `isbn10` varchar(10) NOT NULL default '' COMMENT 'Book ISBN10 (u)',  
  `title` text NOT NULL COMMENT 'Book Title',  
  `title_long` text NOT NULL,  
  `authors` text NOT NULL COMMENT 'Book Authors',  
  `publisher` text NOT NULL COMMENT 'ISBNdb publisher_text',  
  PRIMARY KEY  (`isbn13`),  
  UNIQUE KEY `isbn10` (`isbn10`)  
) ENGINE=InnoDB DEFAULT CHARSET=latin1;  

CREATE TABLE `items` (  
  `item_id` int(10) unsigned NOT NULL auto_increment COMMENT 'Item ID',  
  `rid` int(10) unsigned NOT NULL default '0' COMMENT 'Item Owner User ID',  
  `category_id` int(10) unsigned NOT NULL default '0' COMMENT 'Item Category ID',  
  `title` tinytext NOT NULL COMMENT 'Item Title',  
  `description` text NOT NULL COMMENT 'Item Description',  
  `quality` enum('0','1','2','3','4','5') NOT NULL default '0' COMMENT 'Item Quality',  
  `type` enum('forsale','wanted','pending') NOT NULL default 'pending' COMMENT 'Item Status',  
  `price` int(6) unsigned NOT NULL default '0' COMMENT 'Price',  
  `posted` datetime NOT NULL default '0000-00-00 00:00:00' COMMENT 'Date of Listing',  
  `modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT 'Date of Last Modification',  
  `status` enum('sold','found','flagged','removed','active','expired') NOT NULL default 'active',  
  `item_zip` int(5) unsigned zerofill NOT NULL default '00000',  
  PRIMARY KEY  (`item_id`),  
  KEY `fk_items_rid` (`rid`),  
  KEY `fk_items_category_id` (`category_id`),  
  CONSTRAINT `fk_items_category_id` FOREIGN KEY (`category_id`) REFERENCES `categories` (`category_id`),  
  CONSTRAINT `fk_items_rid` FOREIGN KEY (`rid`) REFERENCES `users` (`rid`)  
) ENGINE=InnoDB AUTO_INCREMENT=123 DEFAULT CHARSET=latin1;  

CREATE TABLE `users` (  
  `rid` int(10) unsigned NOT NULL auto_increment COMMENT 'User ID',  
  `fid` int(10) unsigned NOT NULL default '0' COMMENT 'Facebook User ID',  
  `role_id` int(10) unsigned NOT NULL default '4',  
  `zip` int(5) unsigned zerofill NOT NULL default '00000' COMMENT 'Zip Code',  
  `joined` timestamp NOT NULL default CURRENT_TIMESTAMP COMMENT 'INSERT Timestamp',  
  `email` varchar(255) NOT NULL default '',  
  `notes` varchar(255) NOT NULL default '',  
  PRIMARY KEY  (`rid`),  
  UNIQUE KEY `fid` (`fid`),  
  KEY `fk_users_role` (`role_id`),  
  CONSTRAINT `fk_users_role` FOREIGN KEY (`role_id`) REFERENCES `roles` (`role_id`)  
) ENGINE=InnoDB AUTO_INCREMENT=1013 DEFAULT CHARSET=latin1;  

CREATE TABLE `zip_code` (  
  `id` int(11) unsigned NOT NULL auto_increment,  
  `zip_code` varchar(5) character set utf8 collate utf8_bin NOT NULL,  
  `city` varchar(50) character set utf8 collate utf8_bin default NULL,  
  `county` varchar(50) character set utf8 collate utf8_bin default NULL,  
  `state_name` varchar(50) character set utf8 collate utf8_bin default NULL,  
  `state_prefix` varchar(2) character set utf8 collate utf8_bin default NULL,  
  `area_code` varchar(3) character set utf8 collate utf8_bin default NULL,  
  `time_zone` varchar(50) character set utf8 collate utf8_bin default NULL,  
  `lat` float NOT NULL,  
  `lon` float NOT NULL,  
  `search_string` varchar(52) NOT NULL default '',  
  PRIMARY KEY  (`id`),  
  KEY `zip_code` (`zip_code`)  
) ENGINE=MyISAM AUTO_INCREMENT=42625 DEFAULT CHARSET=utf8;

Answer 1

SELECT  items.item_id,
        items.category_id,
        items.title,
        items.description,
        items.quality,
        items.TYPE,
        items.status,
        items.price,
        items.posted,
        items.modified,
        zip_code.state_prefix,
        zip_code.city,
        books.isbn13,
        books.isbn10,
        books.authors,
        books.publisher
FROM    items
LEFT JOIN
        bookitems
ON      bookitems.item_id = items.item_id
LEFT JOIN
        books
ON      books.isbn13 = bookitems.isbn13
LEFT JOIN
        zip_code
ON      zip_code.zip_code = items.item_zip
WHERE   items.rid = $rid

创建以下索引：

items (rid)
bookitems (item_id)
books (isbn13)
zip_code (zip_code)

Answer 2

您的第一个选择应该是优化数据库。所有40k行都是有用的数据吗？或者可以将一些旧数据移动到包含归档数据的表中吗？你在用适当的索引吗？名单还在继续。

Answer 3

第一个问题是您有哪些索引。您有关于zip_code (zip_code)的索引吗？你的其他桌子有多大？正如我所说的，明显有帮助的索引是zip_code (zip_code)、items (rid)、bookitems (item_id)和books (isbn13)。

我认为您几乎肯定希望在zip_code上建立索引，这很可能用于对该表的任何查询。您还可能需要isbn13和书签(item_id)上的索引。我不知道什么东西应该是(rid)。对此进行索引将有助于查询，但通常可能有用，也可能不有用。

除此之外，查询中没有明显的缺陷。

顺便说一句，“项目离开连接书签.”是多余的。默认情况下，表从左到右连接。