Adding composite indexes won't help there. What you need is an extra redundant table that contains the number of wanted links.
We have a table that contains broken links, which is used in the Most Wanted query. I have no information about the size of these tables in our current database, but I suspect that, again, indexes are not properly used, otherwise the bad performance of this query (which has prompted Magnus to cache the whole page and Brion to create a "miser" mode in which it cannot be loaded) is not explainable.
HANDLER works almost exactly the same as SELECT from a syntax POV.
That's only part of the problem. If anyone wants to mess with this they have to understand what it does and why. That doesn't go away if you have similar syntaxes.
I think your arguments against HANDLER are misguided. We clearly have situations where we do not nead consistent reads, and in these situations, HANDLER may be a great help. As I said, we can abstract the functionality to reduce complexity.
They are currently labeling it as a "gamma". I think it might be worth giving a try, we can always downgrade if it creates problems.
Not if we start using MySQL4 stuff and the problems only start to appear after a while. I agree that the risc is very small, but still.
How about using it on meta only? It is reasonably active, but not mission critical.
- Caching our article index. I have mentioned this idea before, I think:
We could just keep our entire article index in memory *persistently* (currently the linkcache is filled for each request), which should make link lookup almost instant.
What makes you think it isn't already?
I have done tests on the link lookup with a local database and it's very slow with link-heavy pages. This is also what our profiling has shown.
Could you be a bit more specific about that?
see http://www.wikipedia.org/pipermail/wikitech-l/2002-November/001166.html http://www.wikipedia.org/pipermail/wikitech-l/2003-January/002238.html
Regards,
Erik