MySQL slow query

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`

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;

Answer 1

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..

Answer 2

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)

Answer 3

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.

Answer 4

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.