Re: Optimize: most recent N items with join

[Rick James]

And SHOW CREATE TABLE bars ?

The first of these can be removed:
KEY `timestamp` (`timestamp`,`primary_img`),
KEY `anthonyJul01timestamp` (`timestamp`,`primary_img`,`username`,`user_id`),

Probably better off with `private` first, not last:
KEY `LatLong` USING BTREE (`lat`,`lng`,`private`),

tinytext -- some reason for it, instead of VARCHAR(255)?

Serious: `username` is sometimes latin1, sometimes utf8. This will make JOINs on that field not efficient; that is, it won't use the index.

OR is inefficient -- it has to gather lots of stuff and cannot always use available indexes:

Currently:

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;

Getting rid of OR... This _may_ run noticeably faster:

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

You want to fetch type twice?... "SELECT checkins.*, checkins.type as type,"