Optimize: most recent N items with join

[Scott Gatz]

Our site show a latest activity module, which calls up the most recent N reviews, checkins, favorites, and event attendances for users that have photos and displays them on our home page.

I'm having a challenge optimizing one of the queries . It ends up in our slow query logs, taking over 2 seconds. Doing an explain shows that the query is doing a range with our recent_checkins key (using where; using temporary; using filesort)

any advice?

SELECT checkins.*, checkins.type as type, users.gender, IF(users.age=0, DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(users.birthday)), '%Y')+0, users.age ) as age, users.num_reviews, users.facebook_uid, users.birthday, user_images.filename, user_images.caption, t2.name FROM checkins LEFT JOIN users ON checkins.username=users.username LEFT JOIN bars as t2 ON checkins.listing_id=t2.bar_id LEFT JOIN user_images ON users.username=user_images.username WHERE private=0 AND (user_images.primary_img=1 OR users.facebook_uid<>'') GROUP BY checkin_id ORDER BY checkins.created DESC LIMIT 36;

the idea is to fetch the latest checkins, join the users table to add in user info (and see if they have a facebook_id which includes a picture), and join the user_images table to see if they have one or more custom images. we also join the bars table to get the name and link of the entry that they checked in at.


CHECKINS TABLE

CREATE TABLE `checkins` (
  `checkin_id` int(11) NOT NULL auto_increment,
  `username` varchar(32) NOT NULL,
  `metro_id` int(11) NOT NULL,
  `listing_id` int(11) NOT NULL,
  `type` varchar(20) NOT NULL default '',
  `lat` varchar(25) NOT NULL,
  `lng` varchar(25) NOT NULL,
  `shout` tinytext NOT NULL,
  `private` tinyint(1) NOT NULL,
  `facebook` tinyint(1) NOT NULL,
  `twitter` tinyint(1) NOT NULL,
  `created` timestamp NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`checkin_id`),
  KEY `username_checkin` (`username`,`listing_id`,`type`),
  KEY `recent_checkins` (`private`,`created`,`username`,`listing_id`),
  KEY `LatLong` USING BTREE (`lat`,`lng`,`private`),
  KEY `LongLat` USING BTREE (`lng`,`lat`,`private`)
) ENGINE=MyISAM AUTO_INCREMENT=11842 DEFAULT CHARSET=latin1;

CREATE TABLE `users` (
  `userid` int(25) NOT NULL auto_increment,
  `username` varchar(32) collate utf8_unicode_ci NOT NULL default '',
  `password` varchar(255) collate utf8_unicode_ci NOT NULL default '',
  `first_name` varchar(25) collate utf8_unicode_ci NOT NULL default '',
  `last_name` varchar(25) collate utf8_unicode_ci NOT NULL default '',
  `email` varchar(120) collate utf8_unicode_ci NOT NULL default '',
  `bounced_email` varchar(120) collate utf8_unicode_ci NOT NULL default '',
  `age` tinyint(4) NOT NULL default '0',
  `gender` enum('M','F','T','MTF','FTM') collate utf8_unicode_ci default NULL,
  `birthday` date default NULL,
  `geonameid` int(11) NOT NULL default '0',
  `city` varchar(50) collate utf8_unicode_ci NOT NULL,
  `state` varchar(50) collate utf8_unicode_ci NOT NULL,
  `country` varchar(50) collate utf8_unicode_ci NOT NULL,
  `num_reviews` int(11) NOT NULL default '0',
  `num_first_reviews` int(11) NOT NULL default '0',
  `blogurl` varchar(255) collate utf8_unicode_ci NOT NULL default '',
  `postalcode` varchar(11) collate utf8_unicode_ci default NULL,
  `phone` varchar(25) collate utf8_unicode_ci NOT NULL,
  `location` varchar(100) collate utf8_unicode_ci NOT NULL default '',
  `originally_from` varchar(100) collate utf8_unicode_ci NOT NULL default '',
  `description` text collate utf8_unicode_ci NOT NULL,
  `favorite_places` varchar(250) collate utf8_unicode_ci NOT NULL default '',
  `relationship` char(1) collate utf8_unicode_ci NOT NULL,
  `cookie` varchar(32) collate utf8_unicode_ci NOT NULL default '',
  `auth_token` varchar(32) collate utf8_unicode_ci NOT NULL,
  `facebook_uid` varchar(12) collate utf8_unicode_ci NOT NULL default '',
  `facebook_key` varchar(40) collate utf8_unicode_ci NOT NULL default '',
  `facebook_access_token` varchar(255) collate utf8_unicode_ci NOT NULL,
  `twitter_username` varchar(35) collate utf8_unicode_ci NOT NULL,
  `twitter_id` int(11) NOT NULL,
  `twitter_token` varchar(255) collate utf8_unicode_ci NOT NULL,
  `twitter_token_secret` varchar(255) collate utf8_unicode_ci NOT NULL default '',
  `session` varchar(32) collate utf8_unicode_ci NOT NULL default '',
  `ip` varchar(15) collate utf8_unicode_ci NOT NULL default '',
  `referrer` varchar(255) collate utf8_unicode_ci NOT NULL default '',
  `page_name` varchar(50) collate utf8_unicode_ci NOT NULL default '',
  `signup_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `last_login` datetime NOT NULL default '0000-00-00 00:00:00',
  `is_iphone_user` tinyint(1) default '0',
  `is_guest_reviewer` tinyint(1) NOT NULL default '0',
  `activated` enum('0','1') collate utf8_unicode_ci NOT NULL default '0',
  `noindex` tinyint(1) NOT NULL default '0',
  `allow_newsletters` tinyint(1) NOT NULL default '1',
  `allow_contestnewsletters` tinyint(4) NOT NULL default '0',
  `allow_msgnotification` tinyint(1) NOT NULL default '1',
  `allow_generalnotification` tinyint(1) NOT NULL default '1',
  `signup_email_sent` enum('0','1') collate utf8_unicode_ci NOT NULL default '0',
  `temp_password` varchar(50) collate utf8_unicode_ci NOT NULL default '',
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `facebook_update_status` tinyint(4) NOT NULL default '0',
  `check_friends` tinyint(4) NOT NULL default '0',
  PRIMARY KEY  (`userid`),
  UNIQUE KEY `username` USING BTREE (`username`,`facebook_uid`),
  KEY `anthonyJul01` (`userid`,`timestamp`),
  KEY `AnthonyJul01B` (`num_reviews`,`userid`),
  KEY `num_reviews` (`num_reviews`),
  KEY `facebook_uid` USING BTREE (`facebook_uid`,`username`),
  KEY `name` USING BTREE (`first_name`,`last_name`)
) ENGINE=MyISAM AUTO_INCREMENT=55427 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `user_images` (
  `image_id` int(11) NOT NULL auto_increment,
  `username` varchar(25) collate utf8_unicode_ci NOT NULL default '',
  `user_id` int(11) default NULL,
  `filename` tinytext collate utf8_unicode_ci NOT NULL,
  `width` int(11) NOT NULL default '0',
  `height` int(11) NOT NULL default '0',
  `caption` text collate utf8_unicode_ci NOT NULL,
  `primary_img` tinyint(1) NOT NULL default '0',
  `active_img` tinyint(1) NOT NULL default '0',
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`image_id`),
  KEY `timestamp` (`timestamp`,`primary_img`),
  KEY `username` (`username`,`primary_img`,`timestamp`),
  KEY `anthonyJul01timestamp` (`timestamp`,`primary_img`,`username`,`user_id`),
  KEY `anthonyJul01userid` (`user_id`,`timestamp`,`primary_img`),
  KEY `recent_primary_img` (`primary_img`,`username`,`timestamp`)
) ENGINE=MyISAM AUTO_INCREMENT=21339 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Edited 2 time(s). Last edit at 08/03/2010 01:50PM by Scott Gatz.