MySQL slow query

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`

I am running this query to get the list of a user's items and their location. The zip_code table has over 40k records and this might be the issue. It currently takes up to 15 seconds to return a list of about 20 items! What开发者_高级运维 can I do to make this query more efficient?

UPDATE: The following is the table creation code for the relevant tables. Sorry about the formatting!

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

Create the following indexes:

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

Your first option should be to optimise your database. Is all those 40k rows useful data? Or can you move some old data to a table containing archived data? Are you using proper indexing? The list goes on..

The first question is what indexes you have. Do you have an index on zip_code (zip_code) ? How big are your other tables? Indexes that would obviously help are, as I say, zip_code (zip_code), then items (rid), bookitems (item_id), and books (isbn13).

I'd think you would almost certainly want on index on zip_code, that's likely used in almost any query against that table. You likely also want indexes on isbn13 and bookitems (item_id). I don't know what items (rid) is supposed to be. An index on that would help this query, but might or might not be generally useful.

Besides that, there are no obvious flaws in the query.

BTW, the parentheses around "items left join bookitems ..." are superfluous. Tables are joined left to right by default.

I am not sure about this just by looking at the query, but

It seems you are joining your rows with string data types, that is a bit slower to compare than using integers, like IDs, specially if not indexed.