MySQL Join Optimization

Question

Can you please help me optimize this query. I use this query to get a list of friends along with their details and their status. It takes about 0.08 secs to process this on a Athlon X2 6000

I cant use materizlized view as well because this is frequently changing.

SELECT p.userid, p.firstname, p.lastname, p.gender, p.dob, x.relationship,
 IF(p.picture !=1, 
   IF(p.gender != 'm','/sc/f-t.jpg','/sc/m-t.jpg'),
   concat('/sc/pthumb/', p.userid, '.jpg' )) AS picture
  FROM `social` AS p
  LEFT JOIN `friendlist` AS f1 ON (f1.`userid` = p.`userid` AND f1.`friendid` = 1 AND `f1`.`status` = 1)
  LEFT JOIN `friendlist` AS f2 ON (f2.`friendid` = p.`userid` AND f2.`userid` = 1 AND `f2`.`status` = 1)
  LEFT JOIN `x_relationship` AS x ON (x.`id` = p.`relationship`)
  LEFT JOIN `auth` as a ON (a.`userid` = p.`userid`) 
  WHERE 1 AND (f1.`userid` IS NOT NULL OR f2.`userid` IS NOT NULL AND ((a.`banned` != 1 AND a.`deleted` != 1)))
  ORDER BY RAND() LIMIT 0,10

Answer 1

This is your original query, formatted. Below it are a few thoughts.

SELECT 
  p.userid, 
  p.firstname, 
  p.lastname, 
  p.gender, 
  p.dob, 
  x.relationship,
  IF(p.picture !=1, IF(p.gender != 'm', '/sc/f-t.jpg', '/sc/m-t.jpg'), concat('/sc/pthumb/', p.userid, '.jpg' )) AS picture
FROM 
  `social` AS p
  LEFT JOIN `friendlist`     AS f1 ON (f1.`friendid` = 1          AND f1.`userid` = p.`userid` AND `f1`.`status` = 1)
  LEFT JOIN `friendlist`     AS f2 ON (f2.`friendid` = p.`userid` AND f2.`userid` = 1          AND `f2`.`status` = 1)
  LEFT JOIN `x_relationship` AS  x ON (x.`id` = p.`relationship`)
  LEFT JOIN `auth`           AS  a ON (a.`userid` = p.`userid`) 
WHERE 
  1 
  AND (
    f1.`userid` IS NOT NULL 
    OR f2.`userid` IS NOT NULL 
    AND (a.`banned` != 1 AND a.`deleted` != 1)
  )
ORDER BY 
  RAND() 
LIMIT 
  0,10

Think if some of your left joins could be inner joins. If so, change them.

Your WHERE clause is messed up. You are mixing AND and OR without properly prioritizing with parentheses. Try:

WHERE 
  a.`banned` != 1 
  AND a.`deleted` != 1
  AND (
    f1.`userid` IS NOT NULL 
    OR f2.`userid` IS NOT NULL
  )

You could also try:

  INNER JOIN `auth` AS  a ON (
    a.`userid` = p.`userid` 
    AND a.`banned` != 1 
    AND a.`deleted` != 1
  ) 
WHERE 
  f1.`userid` IS NOT NULL 
  OR f2.`userid` IS NOT NULL

You seem to be joining against friendlist solely to check for record existence. You might want to give this a try as well:

FROM 
  `social` AS p
  INNER JOIN `auth`           AS  a ON (
    a.`userid` = p.`userid`
    AND a.`banned` != 1 
    AND a.`deleted` != 1
  ) 
  LEFT  JOIN `x_relationship` AS  x ON (
    x.`id` = p.`relationship`
  )
WHERE 
  EXISTS (
    SELECT 1 FROM `friendlist` WHERE `friendid` = 1 AND `userid` = p.`userid` AND `status` = 1
  ) 
  OR EXISTS (
    SELECT 1 FROM `friendlist` WHERE `friendid` = p.`userid` AND `userid` = 1 AND `status` = 1
  )

ORDER BY RAND() might not be the fastest thing on earth, but if this is what you need... Try ordering by a indexed column to see how much impact ORDER BY RAND() has.

Indexing:

• You should create a composite index on friendlist(friendid, userid, status).
• Make sure there is an index on relationship(id)
• Make sure there is an index on auth(userid)