I have fixed the bug that was causing the ridiculous slowdown when viewing member profiles. It turns out that a particular WHERE clause in one of the queries was slowing things down too much, and after catching the culpreit, I was able to perform the query in PHP instead.

The speed of finding unanswered threads has also been rectified, unfortunately at the cost of showing the results as threads instead of as posts.

Hmm.. so was it the number of round trips made to the database or a logic problem ?

Neither, actually. I had an SQL statement of the type:
For whatever reason, it was taking a ridiculously long amount of time, despite having a MySQL index on the table column. So what I went and did was remove the first WHERE clause and instead sanitized the $php_array array to never include the element 123 before the sql query even runs. (I needed $php_array to include element 123 elsewhere in the script except for this particular SQL query which is why I did it that way to begin with.)

Okay so it was like 'performing operations which should never have been perfomed by the sql' sort of thing.

BTW indexing doesn't account for bad code you know...

Don't pick on me MySQL had to examine COLUMN anyways. It's not my fault it did a brute force search on all of the rows twice just because I wanted to compare COLUMN against a variable and an array.

I certainly don't think it would be traversing the table twice, but I guess you know your tools the best.

BTW why not just use a simple IN clause ?

I'm thinking it would traverse all rows eliminating those where column is 123, and then go back on all of the others and check whether the value of column is in the array, so essentially that's going over nearly everything twice, no?

Well I did use a FIND_IN_SET() clause?

So you mean to say that every condition put in the WHERE clause results in a full table scan.. ? Atleast not in Oracle.

So if I were to use: would this query scan entire table containing millions of records thrice ? Logically, don't think so....

Is there no simple IN clause in MySQL ? Something like the one written by me in the above snippet ?

FIND_IN_SET() appears to be similar to your IN() clause. However, you have to understand that this whole query is meant to determine which a member's favorite forums are and other members interested in the same forums. Therefore, we're searching 300,000+ rows first to see if one column isn't equal to a certain value (cuts out about 10%) and then seeing if the column value is in an array (searching the remaining 90%).