ansaurus

Question

MySQL subqueries

Answer 1

A:

you can store vote sum and post count in a 'users' table and update them via trigger or 'update' query.

Maksim Burnin 2010-05-07 12:59:53

i will test it, thank you

swamprunner7 2010-05-07 21:44:19

Answer 2

+2 A:

Yeah, it's possible:

SELECT login, post_n,

SUM(vote) as votes,

FROM users 
JOIN posts using(id)
LEFT JOIN votes using(post_n)
WHERE visibility=2 OR visibility=3 
GROUP BY login, post_n

Then flatten the result:

select * from 
(
    SELECT login, post_n,

    SUM(vote) as votes,

    FROM users 
    LEFT JOIN posts using(id)
    LEFT JOIN votes using(post_n)
    WHERE visibility=2 OR visibility=3 
    GROUP BY login, post_n
) as votes_count

Then join the comments:

select votes_count.login, votes_count.post_n, votes_count.votes, 

    COUNT(comments.post_n) as comments_count

from 
(
    SELECT login, post_n,

    SUM(vote) as votes,

    FROM users 
    LEFT JOIN posts using(id)
    LEFT JOIN votes using(post_n)
    WHERE visibility=2 OR visibility=3 
    GROUP BY login, post_n
) as votes_count
LEFT JOIN comments using(post_n)
GROUP BY votes_count.login, votes_count.post_n
ORDER BY date DESC LIMIT 0, 15

Michael Buen 2010-05-07 13:10:59

it not looks like it has subqueries but it has. see http://dev.mysql.com/doc/refman/5.0/en/explain.html

Maksim Burnin 2010-05-07 13:14:46

it's not subquery(where the query pingpong back and forth on 3 tables). it's called table deriving, table deriving is more efficient. but table deriving is more elegant if MySQL already has CTE

Michael Buen 2010-05-07 13:18:34

@Maksim: try to discern the difference between table deriving(efficient) and subquery(inefficient)

Michael Buen 2010-05-07 13:20:20

try to have a feel of first class table deriving, download MSSQL or Postgresql and google CTE(common table expression). sadly, MySQL doesn't have CTE yet, so you have to extract intermediate result from other query by enclosing it in parenthesis, this is called table deriving, not subquery

Michael Buen 2010-05-07 13:24:19

@Michael Buen: does it works much quicker than a subquery?

Maksim Burnin 2010-05-07 13:30:17

ohohoh, it`s doesn`t work, errors. and is it really will be more faster? thx for help

swamprunner7 2010-05-07 13:42:29

@Maksim Burnin: yeah table deriving is faster than subquery. think of hard disk read/write that goes back and forth between 3 tables when you use subquery. when you use derived tables, the first derived table from join operation is stored in memory(cached to disk if it's bigger) and subsequent join will join on that in-memory(or disk cached) aggregated result rather than "pingponging" back and forth on the first two tables

Michael Buen 2010-05-07 13:48:11

@swamprunner7: logical error or ambiguity error?

Michael Buen 2010-05-07 13:50:18

Answer 3

A:

i have test both variants and test variant when we using join or where to merge our tables.

No subqueries variant more slow, 0.0208 sec, and mysql use only 271 rows in votes table, but when i have use joins it use whole rows. Here no subqueries variant:

SELECT res.*, COUNT(*) FROM
(
  SELECT login, posts.post_n, SUM(vote)AS votes
  FROM users, posts, votes
  WHERE users.id=posts.id AND posts.post_n=votes.post_n AND visibility=3
  GROUP BY posts.post_n
)AS res, comments
WHERE comments.post_n=res.post_n
GROUP BY res.post_n
ORDER BY date DESC
LIMIT 0, 15

And subqueries varian performed only 0.0027 sec, it`s no cache but using indexes in all tests.

p.s. sorry for my english

swamprunner7 2010-05-07 21:41:14

ansaurus

tags:

views:

answers:

MySQL subqueries

related questions