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