Im more concerned with 400k versus say 20k records. :-) And yes I know the fetch is O(log n). Also i suspect you will find that the log involved is not base 10 but rather base 2.

E:\.cpan\build\Module-Build-0.2602>perl -e"print log 2_000_000 - log 1
+_000_000"
14.5086508307451
[download]

So im still wondering if i should expect to see speedup on this query. I mean we execute it hundreds of time per page fetch.

Oh, sorry for confusion. Actually, the log involved in your code is a natural log (base e), and the log involved in the discussion is of course base 2. Log base 10 is rarely relevant in anything but counting decimal digits :) Sorry once again, that was the reason I did not write the formula in Perl.

400k vs. 20k means 19 vs 15 seeks in the index (or steps in the binary tree). Do you think it will worth it?

What would be more interesting is to hear about the whole caching infrastructure here on PM. Hundreds of cache-checks per web page sounds like a symptom of something rather strange happening. I'd be very tempted to employ memcached, implement cache-checks pipelining and even try to eliminate checks altogether. If we invalidate the cache for a single node when the node is updated we won't need no versions and no checks at all.

P.S. Your code does not calculate what you think it should. The output equals to log (2000000 - log 1000000).

First thanks for the clarification, both of them. The ps one is a bit embarrassing to be honest, it seems like log has a silly prototype and I dont know why but im was expecting "log" to return base 2, and ln() to return base e. I wonder where i got that meme? Hmm.

D:\Dev>perl -e" print prototype 'CORE::log'"
;$
[download]

Shouldnt that really be a prototype of $? /grr its the "default-to-$_" behaviour that makes the parens mandatory.

Ok, re: caching infrastructure. First you have to realize that caching occurs on a per httpd level, and that we have two physicial web servers and a dedicated DB server running the site. The consequence of this of course is that there is no way to do an "update-spoils-the-cache" process. The httpd doing the updating has no way to talk to the other httpd's except through the DB table.

To make things more interesting you have to realize that a good chunk of what you see here on pm is built up out of code that is stored in nodes much as the text of this reply is stored in a node. This code is evaled for every use, and before it is evalled the version is fetched. So to give you an example "parselinksinchatter" might be used three or four times in a given page display, and before each use the version table gets queried.

Now I have been working on a means of caching compiled forms of the code nodes and ensuring that version checks only occur once per page fetch for code nodes, but i thought pruning the version table might also be a quick win. Overall I can see from the general points raised in the replys that its not going to make much if any difference.

Thanks to all who replied.

---
demerphq