Well, the killer is your bad choice of putting multiple countries into one field. "Easiest" is often not the best in database design, and multi-valued fields are one of the biggest no-nos around. You wouldn't get away with that in any even medium scale corporate operation.
I'm not sure we can fix the performance problem so long as you have that multi-valued field, but we can at least make a stab at it.
You missed answering one question:
> Do you really want to return offers.category if the howmany value will be zero?
though I'm not sure it makes much performance difference.
*MAYBE* this query will be more efficient than yours. No promises. That
is still a killer.
$sql = "SELECT OS.category, IFNULL(SS.thecount, 0) AS howmany "
. " FROM offers AS OS LEFT JOIN ( "
. " SELECT O.category, COUNT(*) AS thecount "
. " FROM offers AS O LEFT JOIN completed_offers AS C"
. " ON ( O.offerid = C.offerid AND C.username='$usr' ) "
. " WHERE O.countries LIKE '%$country%' "
. " AND C.offerid IS NULL "
. " GROUP BY O.category ) AS SS "
. " ON OS.category = SS.category "
. " ORDER BY OS.category";
The main thing I changed was to use a LEFT JOIN instead of you IN(...) to combine offers and completed_offers. If this were SQL Server, either would give you the same efficiency. But with MySQL, IN(...) can sometimes not work very well. The trick here is that even though I'm using a LEFT JOIN, the
AND C.offerid IS NULL
turns it into a sort of reverse INNER JOIN.
And since I was then doing that as a LEFT JOIN, it made sense to move the counting "down" one level, thus avoiding the need for the
(also an expensive operation).
That's all the tricks I can see, assuming I got the query right, short of normalizing you DB design.