Shanti
9-27-06, 07:02 PM
I'm holding a little contest on my phpBB message boards and I wanted to throw together a quick and dirty query to pull up a list of the contestants based on the people who've sent a PM to an account I created for the purpose of this contest. The catch is, I want to eliminate from the running anyone who's sending from a duplicate account or who sent more than one PM.
So, I wrote a script that pulls username, email and the PM text for all of the PMs sent to this account and loops the results into a table for display, but that's all it's doing. This is the query I'm using to do that:
SELECT DISTINCT username, user_email, privmsgs_subject, privmsgs_text
FROM phpbb_privmsgs
INNER JOIN phpbb_users ON user_id = privmsgs_from_userid
INNER JOIN phpbb_privmsgs_text ON privmsgs_text_id = privmsgs_id
WHERE privmsgs_to_userid = 1055
ORDER BY privmsgs_id
When I tried to take each ID in that results set and see if anything came back if the IP of any of the posts associated with any of the other PM senders matched any of the IPs that person had posted from, I ran into trouble. When I run this query it spikes the mysql process and pegs my server for about two minutes (I'm developing this locally before uploading it):
SELECT * FROM phpbb_posts
WHERE poster_id = 379
AND poster_ip IN
(SELECT poster_ip
FROM phpbb_posts
WHERE poster_id != 379
AND poster_id IN
(SELECT privmsgs_from_userid
FROM phpbb_privmsgs
WHERE privmsgs_to_userid = 1055 AND privmsgs_type IN (1, 0)
)
)
hehe Before you yell at me too much I'm still pretty much a SQL novice, and the majority of my work has been with MSSQL but even so I do recognize that nesting two subqueries that way is a really inefficient way to get this data. I know there's got to be a way that I can do this in one query. If anyone out there who could give me some pointers as to how I'd structure a query to get what I'm looking for, I would really appreciate the help!
So, I wrote a script that pulls username, email and the PM text for all of the PMs sent to this account and loops the results into a table for display, but that's all it's doing. This is the query I'm using to do that:
SELECT DISTINCT username, user_email, privmsgs_subject, privmsgs_text
FROM phpbb_privmsgs
INNER JOIN phpbb_users ON user_id = privmsgs_from_userid
INNER JOIN phpbb_privmsgs_text ON privmsgs_text_id = privmsgs_id
WHERE privmsgs_to_userid = 1055
ORDER BY privmsgs_id
When I tried to take each ID in that results set and see if anything came back if the IP of any of the posts associated with any of the other PM senders matched any of the IPs that person had posted from, I ran into trouble. When I run this query it spikes the mysql process and pegs my server for about two minutes (I'm developing this locally before uploading it):
SELECT * FROM phpbb_posts
WHERE poster_id = 379
AND poster_ip IN
(SELECT poster_ip
FROM phpbb_posts
WHERE poster_id != 379
AND poster_id IN
(SELECT privmsgs_from_userid
FROM phpbb_privmsgs
WHERE privmsgs_to_userid = 1055 AND privmsgs_type IN (1, 0)
)
)
hehe Before you yell at me too much I'm still pretty much a SQL novice, and the majority of my work has been with MSSQL but even so I do recognize that nesting two subqueries that way is a really inefficient way to get this data. I know there's got to be a way that I can do this in one query. If anyone out there who could give me some pointers as to how I'd structure a query to get what I'm looking for, I would really appreciate the help!