How to improve my friend list MySQL query?

Question

I have a big MySQL query which actually returns the good set of result but it is quite slow.

SELECT DISTINCT
  username,
  user.id as uid,
  email,
  ui.gender,
  ui.country,
  ui.birthday,
  IF( last_activity_date >= now() - INTERVAL 1 HOUR, 1, 0) as is_online,
  p.thumb_url,
  friend_request.id as sr_id,
  IF( ul.id IS NOT NULL, 1, 0) as st_love,
  DATE_FORMAT( NOW(), '%Y') - DATE_FORMAT( birthday, '%Y') - (DATE_FORMAT( NOW(), '00-%m-%d') < DATE_FORMAT( birthday, '00-%m-%d')) AS age

FROM friend_request

JOIN user ON (user.`id` = friend_request.`to_user_id`
                   OR
                   user.`id` = friend_request.`from_user_id`)
              AND user.`id` != '$user_id'
              AND friend_request.`status` = '1'

JOIN user_info ui ON user.`id` = ui.`user_id`

JOIN photo p ON ui.`main_photo` = p.`id`

LEFT JOIN user_love ul ON ul.`to_user_id` = user.`id`
                      AND ul.`from_user_id` = $user_id

WHERE (friend_request.`to_user_id` = '$user_id'
   OR  friend_request.`from_user_id` = '$user_id')
ORDER BY friend_request.id DESC
LIMIT 30

"$user_id" is the id of the logged-in user.

Here is the table structure of "friend_request" :

CREATE TABLE `friend_request` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `from_user_id` int(11) DEFAULT NULL,
  `to_user_id` int(11) DEFAULT NULL,
  `date` datetime DEFAULT NULL,
  `seen` int(11) DEFAULT '0',
  `status` int(11) DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `from_user_id` (`from_user_id`),
  KEY `to_user_id` (`to_user_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 ;

Can you help me to improve this query?

I have not copied the table structure of the other tables because, after some tests, the "optimization issue" seems to come from the friend_request table.

Thanks!

EDIT :

Here is what "EXPLAIN" gives me :

Answer 1

I'd write the query like this. (I'd also qualify the references to all columns in the query, username, email, last_activity_date). I'd also be doing this as a prepared statement with bind placeholders, rather than including the $user_id variable into the SQL text. We're assuming that the contents of $user_id is known to be "safe", or has been properly escaped, to avoid SQL injection.

  SELECT username
       , user.id  AS uid
       , email
       , ui.gender
       , ui.country
       , ui.birthday
       , IFNULL(( last_activity_date >= NOW() - INTERVAL 1 HOUR),0) AS is_online
       , p.thumb_url
       , sr.id                                       AS sr_id
       , ul.id IS NOT NULL                           AS st_love
       , TIMESTAMPDIFF(YEAR,ui.birthday,DATE(NOW())) AS age
    FROM (
           ( SELECT frf.id
                  , frf.to_user_id   AS user_id
               FROM friend_request frf
              WHERE frf.to_user_id   <> '$user_id'
                AND frf.from_user_id  = '$user_id'
                AND frf.status = '1'
              ORDER BY frf.id DESC
              LIMIT 30
            )
            UNION ALL
            ( SELECT frt.id
                   , frt.from_user_id AS user_id
                FROM friend_request frt
               WHERE frt.from_user_id <> '$user_id'
                 AND frt.to_user_id    = '$user_id'
                 AND frt.status = '1'
               ORDER BY frt.id DESC
               LIMIT 30
            )
            ORDER BY id DESC
            LIMIT 30
         ) sr
    JOIN user ON user.id = sr.user_id
    JOIN user_info ui ON ui.user_id = user.id
    JOIN photo p ON p.id = ui.main_photo
    LEFT
    JOIN user_love ul
      ON ul.to_user_id = user.id
     AND ul.from_user_id = '$user_id'

   ORDER BY sr.id DESC
   LIMIT 30

NOTES:

The inline view sr is intended to get at most 30 rows from friend_request, ordered by id descending (like the original query.)

It looks like the original query is intending to find rows where the specified $user_id is in either the from_ or to_ column.

Older versions of MySQL can generated some pretty obnoxious execution plans for queries involving OR predicates for JOIN operations. The usual workaround for that is to use a UNION ALL or UNION operation of the return from two separate SELECT statements... each SELECT can be optimized to use an appropriate index.

Once we have that resultset from sr, the rest of the query is pretty straightforward.

Answer 2

You should look at the query plan or try using the key word Explain https://dev.mysql.com/doc/refman/5.5/en/using-explain.html so that you can find what parts of your query are taking the longest and optimize them.

Things that jump out to me:

1) You may need to reduce your joins, or optimize them

2) You might need some indexes

3) You have an OR statement in your where clause, which may affect the cache query plan, I have seen an Or cause issues with query caching in tsql. Not sure if that would affect mysql. https://dev.mysql.com/doc/refman/5.1/en/query-cache.html

edit: formatting, found out the photo table join was necessary for the data that was being selected