Combining two MySQL queries into one

Question

In my database, I have two tables: Replies and Threads.

I'm trying to make a Contributions page showing what threads users have started or replied to.

Inside Replies are "ThreadId" and "Poster" which specify the Id of the thread it was replied to, and each row in Threads has an Id column as well as a "Poster" column.

I'm trying to make a query to get the rows of all THREADS in which a user has either posted or posted in.

Example:

$myUsername = "Bob";
$q = mysql_query("SELECT * FROM `Threads` WHERE `Poster`='$myUsername'
OR (another query to find if they had a row in Replies matching `ThreadId`='$tid'
AND `Poster`='$myUsername')");

Thanks

Post SHOW CREATE TABLE Threads and SHOW CREATE TABLE Replies — rkosegi, Jun 01 '12 at 09:00
post your structure on http://sqlfiddle.com/ and we will be glad to answer — Muhammad Raheel, Jun 01 '12 at 09:14

score 0 · Accepted Answer · answered Jun 01 '12 at 09:05

0

You will need to use an IN clause to specify that the thread id should be in those returned by a query in the replies table. Something like this is I understand correctly the structure of your tables.

SELECT * FROM Threads WHERE Poster = $myUsername OR Id IN (
SELECT ThreadId FROM Replies WHERE Poster = $myUsername
)

answered Jun 01 '12 at 09:05

Christina

3,562
3
22
32

This query takes a very long time to load and uses a large amount of CPU. I have over 10,000 rows in Threads and 100,000 rows in Replies. Is it the query or my db? – Anonymous Jun 01 '12 at 09:11
Do you have the necessary indexes in place in order for the query to run faster? Can you see which parts of the query take the longest to execute? – Christina Jun 01 '12 at 09:16
I do not know how to see which parts take the longest to execute, but I tried replacing "SELECT * FROM" to simply "SELECT `Id` FROM" which is the index of the rows, and it still loaded slowly. – Anonymous Jun 01 '12 at 09:18
Are you using some graphical tool to access your DB (ex. MySQL Wrokbench)? You should be able to use your tool to "explain" the query and see exactly which steps MySQL goes through to execute it. Indexes don't have to do with the SELECT part of your query but with the WHERE part so it shouldn't make much difference whether you select the whole record or just the id assuming your records aren't really huge. – Christina Jun 01 '12 at 09:20
I use phpMyAdmin, but I wouldn't know how to do that. :/ – Anonymous Jun 01 '12 at 09:21
I ran both of the queries separately but both loaded instantly... however, when combining them in the query you provided, it never loads and brings MySQL to an 89% CPU usage. – Anonymous Jun 01 '12 at 09:22
I think there are already some other questions in stackoverflow on optimising your queries in MySQLm for example http://stackoverflow.com/questions/586381/mysql-not-using-indexes-with-where-in-clause or http://stackoverflow.com/questions/4771035/mysql-query-in-clause-slow-on-indexed-column Perhaps you can get more information from there. – Christina Jun 01 '12 at 12:20

score 0 · Answer 2 · answered Jun 01 '12 at 09:52

Using Christina's solution probably won't result in a very efficient query. The following should be better:

SELECT * 
FROM Threads t1
WHERE t1.Poster='$myUsername'
UNION
SELECT *
FROM Threads t2
INNER JOIN replies r
ON t2.id=r.thread_id
WHERE t2.Poster<>'$myUsername'
AND r.Poster='$myUsername';

However:

1) do you really need all the columns from the threas table? Even if you do, using '*' is messy

2) you are still going to get duplicates if the user made more more than one reply - but this can't be fixed while you are using '*'

3) the query will still be slow eith large amounts of data if you don't have an index on poster in both tables.

score 0 · Answer 3 · answered Jun 01 '12 at 16:33

I have done this differently. The two queries provided returned duplicates and what not, so I've decided to go a little longer way.

What I did:

1) Go through each of the user's replies, and get the thread id

2) Build a query string using these IDs, e.g. "OR Id=$threadId OR Id=$threadId2" etc

3) Put the query string into a thread query such as SELECT * FROM Threads WHERE Poster=$user $queryString2

4) Solution. :)

score 0 · Answer 4 · answered Jan 05 '15 at 11:04

0

Try this:

You can use LEFT OUTER JOIN instead of IN operator

SELECT T.* 
FROM Threads T 
LEFT OUTER JOIN Replies R ON T.Id = R.ThreadId AND R.Poster = $myUsername
WHERE T.Poster = $myUsername OR R.Id IS NOT NULL

answered Jan 05 '15 at 11:04

Saharsh Shah

28,687
8
48
83

Combining two MySQL queries into one

4 Answers4