Extremely slow SQL query with subquery and "HAVING"

Question

I have this query where I want to calculate how many entries in the "user" have several entries in the "user names"

SELECT usernames.NAME,
    COUNT(user.id)
FROM user
INNER JOIN user_username ON user.id = user_username.user_id
INNER JOIN usernames ON user_username.user_username_id = usernames.id
WHERE 
    user.datecreated BETWEEN '2016-01-01' AND '2016-09-01'
    AND user.id IN 
        (
            SELECT user_id
            FROM user_username
            GROUP BY user_id
            HAVING COUNT(*) > 1
        )
GROUP BY usernames.NAME
ORDER BY user.id DESC

The request works, but too slowly, any ideas what I could do to speed up this request?

Answer 1

Is the request broken in this way?

CREATE TEMPORARY TABLE QualifiedUserIDs AS (
SELECT user_id
FROM user_username
GROUP BY user_id
HAVING COUNT(*) > 1);

SELECT usernames.NAME,
    COUNT(user.id)
FROM user
INNER JOIN QualifiedUserIDs quids ON quids.user_id = user.id 
INNER JOIN user_username ON user.id = user_username.user_id
INNER JOIN usernames ON user_username.user_username_id = usernames.id
WHERE 
    user.datecreated BETWEEN '2016-01-01' AND '2016-09-01'

GROUP BY usernames.NAME
ORDER BY user.id DESC

Answer 2

The question is a bit vague, but try the following:

SELECT  usernames.name, COUNT(user.id) 
FROM user 
INNER JOIN user_username ON user.id = user_username.user_id
INNER JOIN usernames     ON user_username.user_username_id = usernames.id
WHERE 
    user.datecreated BETWEEN '2016-01-01' AND '2016-09-01'
    AND (SELECT COUNT(*) where user_id = user.id from user_username) > 1

GROUP BY usernames.name
ORDER BY user.id DESC

I don't think you really need to group user_id in order to get users with more user_names.

, . ( user.id), .

SELECT  user.id, COUNT(usernames.name) -- to show the number of usernames a user has 
FROM user 
LEFT JOIN user_username ON user.id = user_username.user_id
LEFT JOIN usernames     ON user_username.user_username_id = usernames.id
WHERE user.datecreated BETWEEN '2016-01-01' AND '2016-09-01'
GROUP BY user.id
HAVING COUNT(usernames.name) > 1
ORDER BY user.id DESC

... user, usernames.name, user_username.user_id, user.id, user_username.user_username_id, usernames.id, user_id=user.id ...