I usually don't post computing tips, but I just learned how to get rid of a problem that has been bothering me off and on for over a year.
The problem: On multiple Macs running OSX 10.6 and/or 10.7, sometimes a large amount of CPU power (and also system memory) is being used by a process called mds. The problem persists over many hours, and on laptops causes the fans to spin and battery to drain.
Some relevant information: MDS and its friend mdworker are part of Apple's Spotlight software. Basically they troll your system looking for changes and recording them in an index. That index is used by Spotlight to help you find things like that email from four years ago describing how do some arcane thing that you couldn't possibly remember, except that it has the phrase "don't cry at this point". Anyway...
The fix: I've tried a number of things over the many months of having this problem. At home, I removed external drives from the Spotlight index and that fixed the problem. To do that, open System Preferences, go to Spotlight, click on the Privacy tab, and add the external drives to the list.
On my work computers I've done the same thing, but still have the problem sometimes. It has been especially bad on my new MacBook Air with no external drives connected.
Doing some searching, I found this very useful bit: LINK.
The crucial thing that post points out is that backup software that works in the background can cause files to be constantly changing. Constantly changing files need to be re-indexed in the view of Spotlight/mds.
For some reason, I'm not allowed to backup however I want at work (e.g, to a Time Machine drive that could be sitting right here in my office). Instead, my IT department installs some software that supposedly backs up my system. Going to /System/Library/Application\ Support and finding the name of that backup software (in this case it is Symantec) and adding that directory to the list of non-indexed places solves the problem. This is equivalent to the case in that link that uses some other backup software, and I bet that other backup systems also trigger the same behavior.
REPEAT: Exclude your backup software's /System/Library/Application\ Support directory from Spotlight indexing in System Preferences -> Spotlight -> Privacy and mds CPU usage should drop to 1% or less.
Sigh of relief.