>I can see the benefit when you're executing a small number of
>enormously expensive queries (and those queries are CPU-bound);
>but that situation is quite rare, IME.
Bingo. I have some self-joins (Order N^2 at worst) on VARCHAR fields,
which, if you have tables with millions of rows, take full CPU of 900
seconds or more sometimes - and I have a need to expand from 7-digit to
9-digit row counts - which could mean 10,000 times slower. I can speed that
up from P3-1ghz to P4-2.4ghz, but not much.
The best way to solve the above problem is to figure out a more efficient
query, of course, or avoid the need for the query in the first place.
Sometimes easier said than done.
Also, the relatively slow speed for inserts with VARCHAR indices would
conceivably be helped; I often insert data into a temp table and then
INSERT INTO SELECT *... from the temp to the other one to get reasonable
performance. I'd love to be able to turn off MVCC for those kinds of
things; that is, instead of seeing one universal insert of 3 million rows,
I wouldn't mind seeing them dribble in one at a time, thereby saving the
overhead of MVCCing them.
Cheers,
Doug