MySQL Forums
Forum List  »  Optimizer & Parser

Re: Optimize: most recent N items with join
Posted by: Rick James
Date: August 08, 2010 10:35AM

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,"

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Optimize: most recent N items with join
1360
August 08, 2010 10:35AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.