According to SEOmoz's article Link Title Attribute and its SEO Benefit:
The title attribute can be used to describe almost any HTML element. A beneficial way to use the title attribute for SEO purposes would be to use it in the link element to provide descriptive text within an anchor tag (which gives you more real estate for your targeted keyword phrases). I would suggest not to duplicate your anchor text (for usability purposes). It's supposed to provide supplementary information and let the users know where the link will direct them to if they click on it. When creating your link titles, optimize for keyword phrases you’re targeting on the linked to page (just as you would with anchor text). Search engines only use them in consideration to the page being linked to, not the page the link is on.
So yes, they will be used in consideration for the content of the page you're linking to. If you're trying to optimize for keywords on your current page, do not use those keywords in your title attributes. It should be the keywords that you're optimizing for on the page the link leads to.
The title attribute isn't specifically for creating tooltips, however most browsers generate tooltips for elements that have a title set. You can use titles on many different elements, so keep the tooltips in mind when using them. It can get very annoying when used on things like <table>, <th>, <tr>, and <td> all at once.
When used for images, the title attribute is less important to SEO than the alt attribute, but more important than the longdesc attribute.
