That is correct, and you could be right about this (I
don't know what the
current database schema looks exactly like, but last time I was involved
this was still ok.) But keep in mind that a composite index also adds a lot
of overhead for updates, takes up more room in the index cache, and doesn't
really add much speed if the extra columns don't have a high selectivity,
i.e., filter out many unwanted records.
True. However, I suspect with some of our queries, our existing single-
field index only has very low selectivity, which makes stuff like "Most
wanted" and "History" so slow. Not using any composite indexes seems like
a bad idea to me, especially given the fact that some of our queries are
run on huge random-sequence tables.
> 2) The HANDLER command. HANDLER, which works with
both InnoDB and MyISAM,
> provides low-level access to tables *without locking*. You get reads, but
> you don't get consistency, which may cause problems when you're dealing
> with critical, heavily modified data. But it seems perfect for our archive
> stuff.
If there are not many updates then this accomplishes
next to nothing, except
that you have made the software more complicated.
HANDLER works almost exactly the same as SELECT from a syntax POV. We have
many updates on the OLD table, and it is still in MyISAM, meaning table-
level locking. So unless we upgrade that particular table to InnoDB (which
Brion seems reluctant to do because of the long downtime), HANDLER may be
worth giving a try.
However, your point regarding complexity is well taken. We could abstract
the HANDLER function in wfQuery using a "consistent read" parameter, by
default being true.
> 3) Upgrading to MySQL4.
Actually I was once in favour of that but Lee
convinced me that as long as
the developers of MySQL don't think it is ready for production, we shouldn't
either. There is only one thing more important than speed and that is
stability.
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.
> 5) 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.
Again, that may have something to do with our index use and we may want to
look into that. Note that I haven't yet benchmarked the new prefill
mechanism.
I suspect the biggest part of the
involved tables is in the cache anyway,
I don't trust automatic caching, especially since our CUR table is huge
and it is only a very small subset of it (the titles) which we need in
memory.
Keeping the software straightforward and
understandable and for newcomers that also want to help with programming is
a big priority for any open source project.
The current link caching mechanism is far from easy to understand, it
might just be considerably less efficient than the alternatives.
Regards,
Erik