CODEWITHSUNDEEP
mysqlsql
marcosh
marcosh

Reputation: 9008

mysql - rewrite query with subqueries

Consider a table Users with columns Id, Name, Surname and a table Actions with columns Ip and Actor. I need to retrieve, for every Ip, the set of users who did as action using that Ip. What I have now looks like:

SELECT a.ip, (
    SELECT GROUP_CONCAT(t.id, '-', t.name, ' ', t.surname) FROM(
        SELECT ud.id, ud.name, ud.surname
    FROM users_data AS ud
    JOIN actions AS a2 ON a2.actor = ud.id
    WHERE a2.ip = a.ip
    GROUP BY ud.id) AS t
    )
    FROM actions AS a
WHERE a.ip != '' AND a.ip != '0.0.0.0'
GROUP BY a.ip

It doesn't work because a.ip is unknown in the where clause in the inner subquery.

Do to performance issues, I need to avoid to use DISTINCT.

Any suggestion?

Upvotes: 0

Views: 94

Answers (3)

marcosh
marcosh

Reputation: 9008

I solved it using this query (still quite slow, so there's still space for improvements...):

SELECT SQL_NO_CACHE t.ip, COUNT(t.id) AS c, GROUP_CONCAT(t.id, '-', t.name, ' ',   t.surname, '-', t.designerAt > 0) FROM ( 
    SELECT a.ip, ud.id, ud.name, ud.surname, u.designerAt
    FROM actions AS a
    JOIN users_data AS ud ON ud.id = a.actor
    JOIN users AS u ON u.id = a.actor
    WHERE a.ip !=  ''
    AND a.ip !=  '0.0.0.0'
    AND a.actor !=0
    GROUP BY a.ip, a.actor
    ) AS t
GROUP BY t.ip

Upvotes: 0

M Khalid Junaid
M Khalid Junaid

Reputation: 64496

You can rewrite your query as 

SELECT n.ip, GROUP_CONCAT( DISTINCT n.your_user SEPARATOR ' -- ') `users` FROM
(  
    SELECT a.ip  AS ip, CONCAT(t.id, '-', t.name, ' ', t.surname) `your_user`
    FROM users_data AS ud
    JOIN actions AS a ON a.actor = ud.id         
) `new_table` n
WHERE n.ip != '' AND n.ip != '0.0.0.0'
GROUP BY n.ip

Note Be aware of that the result is truncated to the maximum length that is given by the group_concat_max_len system variable, which has a default value of 1024

Upvotes: 1

VijayD
VijayD

Reputation: 818

have you tried writing the condition a2.ip = a.ip outside the subquery.? i.e. in the where clause of outer query!

Upvotes: 0

Related Questions