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 条记录，这可能就是问题所在。目前，返回大约 20 项的列表最多需要 15 秒!我可以做些什么来提高这个查询的效率？

更新:以下是相关表的建表代码。抱歉格式化!

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;

最佳答案

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)

关于sql - MySQL慢查询，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/2723086/