Page 1 of 2

Query Help

Posted: Mon May 05, 2008 6:29 am
by shiznatix
I am having trouble writing a query. Basically, I want to find the count of a field if that field has a certain value only once in the table.

Example data: (let "id" and "title" be the 2 fields in the table)
1, one
2, two
3, three
4, one
5, five

I want a query that would get the count of "3" (for ids 2, 3, and 5) because the title "one" exists twice so the 2 rows with that as a title are excluded. I don't know how to go about writing this query so if someone could give a bit of help that would be greatly appreciated.

Re: Query Help

Posted: Mon May 05, 2008 12:40 pm
by EverLearning
This should work. If someone has a better solution i would also like to see it.
[sql]SELECT COUNT(*)     FROM (        SELECT *        FROM TABLE        GROUP BY title        HAVING COUNT(title) = 1    ) AS unique_values;[/sql]

Re: Query Help

Posted: Tue May 06, 2008 1:15 am
by califdon
EverLearning wrote:This should work. If someone has a better solution i would also like to see it.
[sql]SELECT COUNT(*)     FROM (        SELECT *        FROM TABLE        GROUP BY title        HAVING COUNT(title) = 1    ) AS unique_values;[/sql]
Looks good to me! Nice solution.

Re: Query Help

Posted: Tue May 06, 2008 3:37 am
by shiznatix
Using that query as a base I have written this query:

Code: Select all

 
SELECT
    COUNT(id)
FROM
    (
        SELECT
            *
        FROM
            rb_usermap AS um
        
    RIGHT JOIN
        rbf_user AS us
    ON
        um.fk_vb_user_id = us.userid
 
        GROUP BY
            um.fk_vb_user_id
        HAVING
            COUNT(um.fk_vb_user_id) = 1
        AND
            um.added_time >= 1209675601
        AND
            um.added_time <= 1209761999
         AND us.fk_hear_about_us_id = "7"
    ) AS unique_values;
 
oddly, this crashes apache. If I run it in phpMyAdmin then phpMyAdmin just hangs and is unusable until I restart apache. If I run it in the script I am writing then it will crash all of apache. I don't know why or what to do but maybe somebody can shed some light on why this query is so crazy or maybe I am just writing it incorrectly?

Re: Query Help

Posted: Tue May 06, 2008 6:51 am
by EverLearning
Really crashes apache or just hangs it? Its possible that the query takes so long to execute so that script effectively hangs the server.

How much data are your working with(thousands, millions of rows)?
Are the fields used in your conditions indexed?
What is the output of EXPLAIN for your query?

You could post some sample data for us to tinker with.

Re: Query Help

Posted: Wed May 07, 2008 7:50 am
by shiznatix
The problem seams to be coming from the right join. If I take out the right join then everything is ok. The weird thing is that when I use phpMyAdmin to run this with the right join and everything, that tab will just hang. If I press stop and try to go to any page associated with the same domain, it just hangs. The other tabs that are already open are fine so thats weird. When I restart apache all goes back to normal.

rb_usermap has 11,000 rows, rbf_user has 27,000 rows so the tables aren't even that big. Not sure what to make of this. I don't really have sample data so sorry. Any ideas?

Re: Query Help

Posted: Wed May 07, 2008 3:12 pm
by EverLearning
You didn't give an answer to
Are the fields used in your conditions indexed?
What is the output of EXPLAIN for your query?
Other than that, no new ideas so far.

Re: Query Help

Posted: Thu May 08, 2008 2:28 am
by shiznatix
I figured out the crashing thing. It just crashes that database so the other tabs with the same domain but using a different database are fine. A restart of apache was working but now I am just going straight to restarting mysql to fix the problem.

The fields are indexed that would use the index. Here is the query I am using:

Code: Select all

 
EXPLAIN SELECT COUNT( id ) AS signups
FROM (
 
SELECT *
FROM rb_usermap AS um
RIGHT JOIN rbf_user AS us ON um.fk_vb_user_id = us.userid
GROUP BY um.fk_vb_user_id
HAVING COUNT( um.fk_vb_user_id ) =1
AND um.added_time >=1209589201
AND um.added_time <=1209675599
AND us.fk_hear_about_us_id = '7'
) AS unique_values;
 
and here is the output:

Re: Query Help

Posted: Thu May 08, 2008 3:46 am
by EverLearning
The problem is that your query is for every row in rb_usermap table is scanning the whole of the rbf_user table, and thats whats hanging you mysql server.
Also there are no possible keys listed for rbf_user, so are you sure you have indexes in that table, specifically on rbf_user.userid?
You should probably rewrite your query.
Is the following any good?

Code: Select all

SELECT
    COUNT(id)
FROM
    (
     SELECT *
        FROM rbf_user AS us
            LEFT JOIN rb_usermap AS um
               ON um.fk_vb_user_id = us.userid
        WHERE 
            um.added_time BETWEEN 1209675601 AND 1209761999
            AND us.fk_hear_about_us_id = "7"
        GROUP BY
            us.userid
        HAVING
            COUNT(um.fk_vb_user_id) = 1
    ) AS unique_values;

Re: Query Help

Posted: Thu May 08, 2008 6:52 am
by shiznatix
Getting somewhere... the query is not so super slow and crashing mysql like before. The problem now is that it is not returning correct results. My query is as follows:

Code: Select all

 
SELECT COUNT( id ) AS signups
FROM (
 
SELECT *
FROM rbf_user AS us
LEFT JOIN rb_usermap AS um ON um.fk_vb_user_id = us.userid
WHERE um.added_time
BETWEEN 1204322401
AND 1204408799
AND um.fk_room_id = '3'
GROUP BY us.userid
HAVING COUNT( um.fk_vb_user_id ) =1
) AS unique_values
 
The problem is that it returns 10 which is the total users between those 2 added times in rb_usermap. I double checked and it returns results that have more than 1 of the same fk_vb_user_id. The number it should return should be less than 8, maybe like 4 or something but instead it returns the whole thing.

Re: Query Help

Posted: Thu May 08, 2008 6:58 am
by EverLearning
Try

Code: Select all

GROUP BY um.fk_vb_user_id

Re: Query Help

Posted: Thu May 08, 2008 7:08 am
by shiznatix
same exact thing.

Re: Query Help

Posted: Thu May 08, 2008 7:44 am
by EverLearning
I think the problem is in SELECT * part because that returns several rows for one rbf_user row because of the JOIN

Try this

Code: Select all

SELECT COUNT(id) AS signups
    FROM (
        SELECT DISTINCT(us.userid)
            FROM rbf_user AS us
                LEFT JOIN rb_usermap AS um ON um.fk_vb_user_id = us.userid
            WHERE um.added_time
                BETWEEN 1204322401
                    AND 1204408799
                    AND um.fk_room_id = '3'
            GROUP BY us.userid
            HAVING COUNT( um.fk_vb_user_id ) =1
    ) AS unique_values

Re: Query Help

Posted: Thu May 08, 2008 8:18 am
by shiznatix
No go, still the same results. It seams like we are straying away from the idea that I want to get a count from the rb_usermap table not so much from the rbf_user table. I went and tried this query but get an error saying that the collumn id is not found. Humm..

Code: Select all

 
SELECT COUNT( id ) AS signups
FROM (
 
SELECT DISTINCT (
um.fk_vb_user_id
)
FROM rbf_user AS us
LEFT JOIN rb_usermap AS um ON um.fk_vb_user_id = us.userid
WHERE um.added_time
BETWEEN 1204322401
AND 1204408799
AND um.fk_room_id = '36'
GROUP BY um.fk_vb_user_id
HAVING COUNT( um.fk_vb_user_id ) =1
) AS unique_values
 

Re: Query Help

Posted: Thu May 08, 2008 8:54 am
by EverLearning
Thats because you are selecting um.fk_vb_user_id and not id.
Try adding an alias to SELECT DISTINCT

Code: Select all

 
//...
SELECT DISTINCT (um.fk_vb_user_id) AS id
//...