Query Help

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

Query Help

Post 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.
User avatar
EverLearning
Forum Contributor
Posts: 282
Joined: Sat Feb 23, 2008 3:49 am
Location: Niš, Serbia

Re: Query Help

Post 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]
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Query Help

Post 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.
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

Re: Query Help

Post 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?
User avatar
EverLearning
Forum Contributor
Posts: 282
Joined: Sat Feb 23, 2008 3:49 am
Location: Niš, Serbia

Re: Query Help

Post 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.
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

Re: Query Help

Post 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?
User avatar
EverLearning
Forum Contributor
Posts: 282
Joined: Sat Feb 23, 2008 3:49 am
Location: Niš, Serbia

Re: Query Help

Post 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.
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

Re: Query Help

Post 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:
Attachments
Screenshot.png
Screenshot.png (18.08 KiB) Viewed 1218 times
User avatar
EverLearning
Forum Contributor
Posts: 282
Joined: Sat Feb 23, 2008 3:49 am
Location: Niš, Serbia

Re: Query Help

Post 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;
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

Re: Query Help

Post 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.
User avatar
EverLearning
Forum Contributor
Posts: 282
Joined: Sat Feb 23, 2008 3:49 am
Location: Niš, Serbia

Re: Query Help

Post by EverLearning »

Try

Code: Select all

GROUP BY um.fk_vb_user_id
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

Re: Query Help

Post by shiznatix »

same exact thing.
User avatar
EverLearning
Forum Contributor
Posts: 282
Joined: Sat Feb 23, 2008 3:49 am
Location: Niš, Serbia

Re: Query Help

Post 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
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

Re: Query Help

Post 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
 
User avatar
EverLearning
Forum Contributor
Posts: 282
Joined: Sat Feb 23, 2008 3:49 am
Location: Niš, Serbia

Re: Query Help

Post 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
//...
 
Post Reply