How to apply media query in table?

Optimize MySQL query with JOINS and GROUP

• I have been trying to fix this query for weeks and have gotten nowhere, and its dragging my website to a crawl. Part of me is thinking I went a little overboard on the JOINS... We have four tables bookinventory (85,000 records, 5 Megs) and bookitem (90,000 records, 200 Megs), bookusers, and shipto (last two are small tables). CREATE TABLE `bookinventory` ( `ISBN` varchar(32) NOT NULL default '', `userid` int(11) NOT NULL default '0', `itemnum` int(11) NOT NULL auto_increment, `listtime` datetime NOT NULL default '0000-00-00 00:00:00', `status` int(16) default NULL, `sentto` int(11) NOT NULL default '0', `comment` text NOT NULL, `trackback` int(11) default NULL, `condition` varchar(16) NOT NULL default '', PRIMARY KEY (`itemnum`), KEY `time_idx` (`listtime`) ) TYPE=MyISAM AUTO_INCREMENT=122082 ; CREATE TABLE `bookitem` ( `id` int(11) NOT NULL auto_increment, `isbn` varchar(32) NOT NULL default '', `title` varchar(128) NOT NULL default '', `author` varchar(255) NOT NULL default '', `reviews` text NOT NULL, `smallimg` varchar(128) NOT NULL default '', `largeimg` varchar(128) NOT NULL default '', `category` varchar(255) NOT NULL default '', `subject` varchar(255) NOT NULL default '', `releasedate` varchar(32) NOT NULL default '0000-00-00', `publisher` varchar(64) NOT NULL default '', `updatetime` date NOT NULL default '0000-00-00', `media` varchar(32) NOT NULL default '', `description` text NOT NULL, `rating` float NOT NULL default '0', PRIMARY KEY (`id`), KEY `media_idx` (`media`), KEY `isbn_idx` (`isbn`) ) TYPE=MyISAM AUTO_INCREMENT=91640 ; CREATE TABLE `bookusers` ( `userid` int(11) NOT NULL auto_increment, `username` varchar(64) NOT NULL default '', `password` varchar(64) NOT NULL default '', `realname` varchar(128) NOT NULL default '', `street` varchar(128) NOT NULL default '', `city` varchar(64) NOT NULL default '', `state` char(2) NOT NULL default '', `zip` varchar(16) NOT NULL default '0', `points` int(16) NOT NULL default '0', `email` varchar(128) NOT NULL default '', `joindate` date NOT NULL default '0000-00-00', `session` varchar(32) NOT NULL default '', `referer` varchar(64) NOT NULL default '', `newsmail` char(3) NOT NULL default 'ON', `justmailed` char(3) NOT NULL default 'NO', `ip` varchar(32) default NULL, `country` varchar(32) NOT NULL default '', `status` int(11) NOT NULL default '0', `premiumends` date NOT NULL default '0000-00-00', `bookmailer` char(3) NOT NULL default 'OFF', `dvdmailer` char(3) NOT NULL default 'OFF', `cdmailer` char(3) NOT NULL default 'OFF', `vhsmailer` char(3) NOT NULL default 'OFF', `videogamemailer` char(3) NOT NULL default 'OFF', `magazinemailer` char(3) NOT NULL default 'OFF', `paid` int(11) default NULL, `shipoutsidecountry` char(3) NOT NULL default '', `countryfilter` char(3) NOT NULL default 'ON', PRIMARY KEY (`userid`), UNIQUE KEY `uniqueadd` (`street`,`zip`), UNIQUE KEY `userid` (`userid`), UNIQUE KEY `username` (`username`) ) TYPE=MyISAM AUTO_INCREMENT=4218 ; CREATE TABLE `shipto` ( `id` int(11) NOT NULL auto_increment, `userid` int(11) NOT NULL default '0', `country` varchar(32) NOT NULL default '', PRIMARY KEY (`id`), UNIQUE KEY `cat` (`userid`,`country`) ) TYPE=MyISAM AUTO_INCREMENT=91499 ; bookinventory: Holds information about items that someone has, people can have the same item bookitem: Holds common info about an item, like its title bookusers: Holds user info shipto: Holds lists of countries that a user ships to If I run this statement, it can take 10 seconds to run: SELECT bookinventory.ISBN, bookinventory.trackback, bookinventory.userid, bookusers.userid, bookusers.country, bookusers.shipoutsidecountry, DATE_FORMAT( bookinventory.listtime, '%M %D, %Y' ) AS listtime, bookitem. * , shipto. * , count( * ) AS count FROM bookinventory, bookitem, bookusers, shipto WHERE ( bookitem.media = 'Hardcover' OR bookitem.media = 'Paperback' ) AND bookinventory.ISBN = bookitem.isbn AND bookinventory.status IS NULL AND bookinventory.userid != '1' AND bookinventory.userid = bookusers.userid AND ( shipto.userid = bookusers.userid AND shipto.country = 'United States of America' ) GROUP BY bookinventory.ISBN ORDER BY bookitem.title ASC LIMIT 0 , 30 I am grouping by ISBN because multiple people might have the same item, but we want it listed once. Explain of query: table type possible_keys key key_len ref rows Extra bookitem range PRIMARY,media,isbntbl media 32 NULL 51151 Using where; Using temporary; Using filesort bookinventory ref ISBN,browse ISBN 32 bookitem.isbn 1 Using where bookusers eq_ref PRIMARY,userid PRIMARY 4 bookinventory.userid 1 shipto eq_ref cat cat 36 bookusers.userid,const 1 Using where Is there any way to make this query work faster?

• Answer:

  Hello skyhigh-ga, The problem with your query is that you are listing multiple large tables in the FROM clause. So essentially what is happening behind the scenes is this: 1. Tables bookinventory and bookitem are being joined with no join condition 2. The resulting intermediate result contains 90000*85000 records 3. Multiply this by the number of rows in both bookusers and shipto 4. Apply all the filters in your WHERE clause to this massive table I can certainly understand such a query taking 10 seconds or longer! Instead, I suggest you explicitly join your tables so that you can specify a condition when the join actually occurs. The optimized query is below: SELECT bookinventory.ISBN, bookinventory.trackback, bookinventory.userid, bookusers.userid, bookusers.country, bookusers.shipoutsidecountry, DATE_FORMAT( bookinventory.listtime, '%M %D, %Y' ) AS listtime, bookitem. * , shipto. * , count( * ) AS count FROM bookinventory JOIN bookitem ON bookitem.ISBN = bookinventory.ISBN JOIN bookusers ON bookusers.userid = bookinventory.userid JOIN shipto ON shipto.userid = bookusers.userid WHERE (bookitem.media = 'Hardcover' OR bookitem.media = 'Paperback') AND bookinventory.status IS NULL AND bookinventory.userid != '1' AND shipto.country = 'United States of America' GROUP BY bookinventory.ISBN ORDER BY bookitem.title ASC LIMIT 0 , 30 As you can see above, three of the conditions that were previously in the WHERE clause have been turned into conditions in the three new JOIN statements. Also, we are now only starting with a single table and then specifying how we want to join with other tables required in the final resultset. Hopefully this resolves your performance headaches - if you have any concerns about the information above, please post a clarification and I will respond to it promptly. Cheers! answerguru-ga