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 run this query to get a list of user items and their location. The zip_code table has over 40 thousand records, and this can be a problem. It currently takes up to 15 seconds to return a list of approximately 20 items! What can I do to make this request more efficient?
UPDATE: The following is the table creation code for the respective tables. Sorry for 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;