Wedge

The issue with performance is that when you start, you never know how much you're going to gain...

For instance, saving 95% performance on timeformat() is great. Realizing that this means saving less than a millisecond sucks...
Saving 5% in parse_bbc() for that matter, would be much nicer ;)

In our case though... If we're going to worry about filesorts every time we change a query, I don't know about you but I'll just stop doing queries altogether...

But I do worry about it :P But really worrying about it at this stage is not a huge deal, we can always fix it up later if we have better ideas :) (Beta is when we really need to worry about performance)

I guess so.

I just don't want to be pushed back by things like that. I'm already worried enough about game changers like the privacy query. (which I'm always postponing as you can see...)

Don't let yourself be pushed back by it, I'll worry about it :)

Well, after all this time I still don't know whether to use an extra table, or several, or none, and whether to use membergroups for everything, etc, etc... It feels like it's going to be my second 'impossible task' after my failure to make a decision on the AeMe-powered attachment system. (Even though Noisen.com is quite happy to run with my original privacy code...)

There's no best solution at all, only a variety of least worst solutions that all have their up-sides and down-sides to them.

BTW the query seems to execute in a millisecond or two... Really not worth worrying ;)

What does this mean in explain?

Using where; Using filesort

Does it mean it uses an index and then a filesort on the entries it found?

No, that means it's not using any index to filter records (because it's using the WHERE to isolate, rather than an index), and then the results are sorted on a filesort.

Just to clarify, when you normally do sorting, you create a temporary table in memory, perform the sort and return results. When you invoke something that has a text column into a sort, it cannot use a temporary table in memory and does so on disk - by comparison this is much slower. (Note that varchar columns only create on-disk tables if there isn't enough memory to do the sorting in RAM, whereas a text column will always generate a filesort)