OAuth memcache issues

While doing some basic performance analysis on the API, I notice that we're doing five extra database queries for every OAuth hit...

Basic problems

 * 1) we're having to connect to the master database just to work with the 'nonce' value, even though we'd otherwise be working with a read-only request
 * 2) some oauth tables need tweaking to get the lookups optimized for memcached
 * 3) unique indexes
 * 4) multi-column lookups should be avoided unless properly marked up for caching
 * 5) some of these lookups aren't actually indexed and could cause table scans...
 * 6) an ever-growing nonce table seems dangerous

Queries
SELECT * FROM token WHERE ( token.consumer_key = '#' ) AND ( token.tok = '#' ) AND ( token.type = 1 )

That should be pretty cacheable if restructured, may need tweaks on the table for proper unique constraints.

SELECT * FROM nonce WHERE ( nonce.consumer_key = '#' ) AND ( nonce.nonce = '#' ) AND ( nonce.ts = '#' )

The "nonce" is a key value which helps protect against replay attacks if traffic is being sniffed. Changing any of the parameters including the nonce would change the signature, so a valid replay needs to leave it intact. If we see that we've used this nonce before, then we can discard the request as malicious.

Since we expect that most of the time we will not get a hit here, it's not very memcache-friendly.

INSERT INTO nonce (consumer_key, nonce , ts , created ) VALUES ('#' , '#' , '#' , '#')

Now we have to actually record that the nonce was used. This means writing to the master database, even if all we did was a read-only request which turns up no new notices! :(

SELECT * FROM oauth_application WHERE ( oauth_application.consumer_key = '#' ) LIMIT 0, 1

Now in ApiOauth::checkOAuthRequest, we end up pulling some more OAuth data and fetch the application record. This should be very memcache-friendly, but the current implementation needs some tweaks.


 * Oauth_application::getByConsumerKey($consumer)
 * this function does an explicit query instead of a staticGet
 * But consumer_key isn't a unique index on this table anyway, so wouldn't be properly optimized.

SELECT * FROM oauth_application_user WHERE ( oauth_application_user.token = '#' )

Again in ApiOauth::checkOAuthRequest, we pull the user's token info.


 * $appUser = Oauth_application_user::staticGet('token', $access_token);
 * that sounds legit, right?
 * but the 'token' field isn't a unique index on the table, so we can't do a memcache-optimized lookup