CODEWITHSUNDEEP
mysqlsql
user2473500
user2473500

Reputation: 33

How to optimize this my mysql query?

I'm developing a new project. I need a help for the mysql query, I think it is very slow.

SELECT post_id, user_id, content, title, TIME, (

SELECT username
FROM users
WHERE id = posts.user_id
) AS username
FROM posts
WHERE user_id =1
OR user_id
IN (

SELECT DISTINCT user_to
FROM friends
WHERE user_from =1
OR user_to =1
)
ORDER BY posts.post_id DESC 
LIMIT 0 , 10

Thanks for any help.

Upvotes: 3

Views: 76

Answers (3)

user359040
user359040

Reputation:

Try:

SELECT p.post_id, p.user_id, p.content, p.title, p.TIME, u.username
FROM (select distinct us.* 
      from friends f
      join users us on f.user_to = us.id
      WHERE f.user_from =1 OR f.user_to =1) u
JOIN posts p on u.id = p.user_id
ORDER BY p.post_id DESC 
LIMIT 0 , 10

Upvotes: 2

Fabio
Fabio

Reputation: 23480

You can use an INNER JOIN query

SELECT a.post_id, a.user_id, a.content, a,title, a.TIME, b.username
FROM posts a
INNER JOIN users b
ON a.user_id = b.id
WHERE a.user_id =1
OR a.user_id
IN (

    SELECT DISTINCT user_to
    FROM friends
    WHERE user_from =1
    OR user_to =1
)
ORDER BY a.post_id DESC 
LIMIT 0 , 10

This should be faster than your actual query.

Upvotes: 4

Devart
Devart

Reputation: 121902

Try this one -

SELECT  
    p.post_id
  , p.user_id
  , p.content
  , p.title
  , p.`time`
  , u.username
FROM posts AS p
/*LEFT*/ JOIN users AS u ON u.ID = p.user_id
WHERE user_id = 1
    OR user_id IN (
        SELECT DISTINCT user_to
        FROM friends
        WHERE user_from = 1
          OR user_to = 1
    )
ORDER BY p.post_id DESC
LIMIT 0, 10

Upvotes: 4

Related Questions