Based on your comments here I am assuming you are unwilling to change the existing data model because hundreds of applications are using that (and will break if you replace it with something else).
The root of the problem is that for any site, we only know it's direct parent, so we need to lookup the parent of that parent recursively until we found the root site.
If you can get away with a limit to the depth / level to which sites can be nested, you can write one great query that does all the work for you and probably isn't even that slow to boot. Most overhead from firing queries comes from setting up the connection, network bandwidth etc. MySQL can be very quick.
Firing multiple queries multiplies all the overhead, so we don't want that.
Doing a SELECT * and then computing in the application logic means you will fetch all data every time, maximizing network overhead, so we don't want that.
If a limit to the depth of the tree is acceptable, you can combine multiple queries into one huge query that does all the work and returns the exact resultset you need. As an example I used your data, but with A, B, C etc replaced with 1, 2, 3 (as your columns are int).
To get all direct children of the root node (with site_id = 1) do this:
select site_id from site where parent_id = 1
To get the grandchildren of the root node, do this:
select grandchild.site_id
from site grandchild, site child
where grandchild.parent_id = child.site_id
and child.parent_id = 1
To get the great-grandchildren of the root node, do this:
select greatgrandchild.site_id
from site greatgrandchild, site grandchild, site child
where greatgrandchild.parent_id = grandchild.site_id
and grandchild.parent_id = child.site_id
and child.parent_id = 1
To get all descendants of the root node, just combine the above queries into one huge query, like this:
select site_id
from site
where site_id in (
select site_id
from site
where parent_id = 1
)
or site_id in (
select grandchild.site_id
from site grandchild, site child
where grandchild.parent_id = child.site_id
and child.parent_id = 1
)
or site_id in (
select greatgrandchild.site_id
from site greatgrandchild, site grandchild, site child
where greatgrandchild.parent_id = grandchild.site_id
and grandchild.parent_id = child.site_id
and child.parent_id = 1
)
I think you see how this is working. For each extra level, create a query that finds the nodes which are that many levels away from the site you are searching descendants for and add that query to the super-query with an extra 'or site_id in ()'...
Now as you can see, just for three levels, this already becomes a big query. If you need to support, say, 10 levels, this query will become huge and all the OR's and IN's in it will slow it down... However, it still will probably be faster then just getting everything or using multiple queries. If you need to support an arbitrary amount of possible levels than this query cannot help you. It would have to become infinitely large. In that case all that remains is to use a better way...
That said, and before you copy paste this and start coding, there is a way that avoids such huge queries, supporting arbitrary depths and without breaking backward compatibility. It does require a change to the data model but it's a small one that won't hurt the other programs using this data model. There is in short...
A BETTER WAY
Add an extra column parent_paths, using something like ravnur mentioned in his answer to encode the full path from each node all the way up to the root
Fill that column dynamically using triggers on insert, update and delete. You are now maintaining redundant data. It won't hurt other programs but can give a significant performance benefit for yours. Make sure your triggers are bullet-proof though (that's the hardest part probably) as the data in the extra column should always be in synch with the regular data in the table
Use a short and sweet query like the one ravnur showed that looks for the occurance of the site_id at any place in the parent_paths column to directly get all descendants of the site with that site_id without any recursion.
