Arantor

  • As powerful as possible, as complex as necessary.
  • Posts: 14,278
Re: Board order issues
« Reply #30, on April 12th, 2012, 03:35 PM »
But that's still a filesort per time it's viewed - and it is an I/O hit that's not very nice. I don't think it's a performance killer but we should be mindful of it. What it might be worth doing is figuring out whether it's worth doing in PHP rather than MySQL.
When we unite against a common enemy that attacks our ethos, it nurtures group solidarity. Trolls are sensational, yes, but we keep everyone honest. | Game Memorial

Nao

  • Dadman with a boy
  • Posts: 16,063
Re: Board order issues
« Reply #31, on April 12th, 2012, 04:02 PM »
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...

Arantor

  • As powerful as possible, as complex as necessary.
  • Posts: 14,278
Re: Board order issues
« Reply #32, on April 12th, 2012, 04:16 PM »
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)

Nao

  • Dadman with a boy
  • Posts: 16,063
Re: Board order issues
« Reply #33, on April 12th, 2012, 04:40 PM »
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...)

Arantor

  • As powerful as possible, as complex as necessary.
  • Posts: 14,278
Re: Board order issues
« Reply #34, on April 12th, 2012, 06:11 PM »
Don't let yourself be pushed back by it, I'll worry about it :)

Nao

  • Dadman with a boy
  • Posts: 16,063
Re: Board order issues
« Reply #35, on April 12th, 2012, 07:07 PM »
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...)

Arantor

  • As powerful as possible, as complex as necessary.
  • Posts: 14,278
Re: Board order issues
« Reply #36, on April 12th, 2012, 07:48 PM »
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.

Nao

  • Dadman with a boy
  • Posts: 16,063
Re: Board order issues
« Reply #37, on April 12th, 2012, 09:51 PM »
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?

Arantor

  • As powerful as possible, as complex as necessary.
  • Posts: 14,278
Re: Board order issues
« Reply #38, on April 12th, 2012, 10:25 PM »
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)