CODEWITHSUNDEEP
mysqlsqlselectquery-optimization
Toni Michel Caubet
Toni Michel Caubet

Reputation: 20163

Optimize query: Select in Select

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

Upvotes: 1

Views: 1829

Answers (4)

Andrey Tatarinov
Andrey Tatarinov

Reputation: 570

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;

Upvotes: 2

sll
sll

Reputation: 62494

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)

Upvotes: 2

Chris J
Chris J

Reputation: 9252

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

Upvotes: 0

Jan-Henk
Jan-Henk

Reputation: 4874

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

Upvotes: 0

Related Questions