1

Lets say i have a table to store the blogs (table name is blogs) of each user. But also have a table to store the blogs from other users that the user liked (table name is likedBlogs), yes?

so to retrieve them i just do: -Select user's blogs. -Add the blogs he liked.

    $q = mysql_query("
SELECT id 
FROM blogs 
WHERE (iduser = $id) 
      OR id IN 
       (SELECT idblog 
        FROM likedBlogs 
        WHERE iduser='$id') 
      AND (id > 0) 
ORDER BY id DESC 
LIMIT 20
") or die(mysql_error());

Can i do this better? (how would you qualify in performance this query?)

Thanks

Improve this question

4 Answers 4

Reset to default
2

I believe you can better refactor it usign EXISTS instead of IN.

IN requires the entire result set be returned and then begin search for a value, but EXISTS check row by row and interrupt inner query when first occurence is found.

SELECT id  
FROM blogs  
WHERE (iduser = $id)        
OR EXISTS
      (SELECT idblog         
       FROM likedBlogs          
       WHERE iduser='$id' AND idblog = id)
AND (id > 0)  
ORDER BY id 
DESC  LIMIT 20

See Optimizing IN/=ANY Subqueries

very useful optimization is to “inform” the subquery that the only rows of interest are those where the inner expression inner_expr is equal to outer_expr. This is done by pushing down an appropriate equality into the subquery's WHERE clause. That is, the comparison is converted to this: EXISTS (SELECT 1 FROM ... WHERE subquery_where AND outer_expr=inner_expr)

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

@Toni Michel Caubet : yep, significantly on large data sets. See link I've provided, I believe you can search intrawebs for EXISTS vs IN MySql and you'll find a lot of reviews
In SQL Server 2005+ EXISTS and IN evaluate the same, thankfully.
@JNK : wow, good to know, thanks! Just tried to look into MSDN regardign IN and not found anything regarding this, you mean that the same execution plan would be generated?
@sll - yes, and typically it's faster than a JOIN since it short circuits. I use IN for evaluation of a single field and EXISTS for comparison on multiple fields.
Interesting! so @JNK, would you recomend any change in this query?
|
2

Usually you would use join instead of nested select's.

select blogs.id
from blogs inner join likedBlogs on (blogs.iduser = likedBlogs.iduser and blogs.id  = likedBlogs.idblog)
where blogs.iduser = $id
and blogs.id > 0
order by blogs.id desc limit 20

upd first time I didn't read task statement correctly

select blogs.id 
from blogs left join likedBlogs on (blogs.id = likedBlogs.idblog)
where blogs.iduser = $id or likedBlogs.iduser = $id;
Improve this answer

3 Comments

thanks, faster than stackoverflow.com/questions/7902611/… ?
Is INNER JOIN would be valid for cases when no entries in likedBlogs?
Sorry for confusion. left join is what we need in this case. Updated my answer.
0

Your query looks fine to me. For performance you should make sure you have indexes on the id columns in your database tables.

Improve this answer

1 Comment

uf, i am not using indexes yet... i haven't found anything easy to understand yet..
0

I think you might be better served by a union

SELECT id 
FROM blogs 
WHERE iduser = $id AND id > 0
UNION 
SELECT idblog AS id 
FROM likedBlogs 
WHERE iduser='$id' AND idblog > 0
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.