Is there any way to shorten up this Facebook API call?

Right now I am working on an AJAX call that grabs a random photo that a Facebook user is tagged in and then grabs the large source for it. Is there any way to speed this up or at least distill the call into one query?

var query1 = encodeURI("SELECT pid,xcoord,ycoord FROM photo_tag WHERE subject={{mystery.uid}} order by rand() LIMIT 1");    
var query2 = encodeURI("SELECT src_big FROM photo WHERE pid=");

function get_pic() {
        var l = $.get("https://graph.facebook.com/fql?q="+query1, 
                {access_token: "{{token}}"},
                function(data1) {
                        $.get("https://graph.facebook.com/fql?q="+query2+data1.data[0].pid, 
                        {access_token: "{{token}}"},
                        function(data2) {
                                $("#mystery_img").html('');
                                var top = $("#mystery_img img").offset().top + Math.floor(data1.data[0].ycoord);
                                var left = $("#mystery_img img").offset().left + Math.floor(data1.data[0].xcoord);
                                var img = $(document.createElement('img')).attr({src: "/static/images/curved_arrow.gif"})
                                    .css('position', 'absolute')
                                    .css('top', top)
                                    .css('left', left);
                                    $("#mystery_img").append(img);
                        }, 
                        "json");
                }, 
                "json");
    }

I haven't worked with Facebook's API using queries like this, but I know SQL inside and out, and this screams INNER JOIN to me.

var joinedQuery = "SELECT Photo_Tag.pid,Photo_Tag.xcoord,Photo_Tag.ycoord,Photo.src_big FROM photo_tag AS Photo_Tag INNER JOIN photo AS Photo ON Photo_Tag.pid = Photo.pid WHERE Photo_Tag.subject = {{mystery.uid}} ORDER BY RAND() LIMIT 1;";

Also, the use of ORDER BY RAND() is going to be inherently slow (especially with a system as large as Facebooks), so might I recommend something more like this:

var joinedQuery = "SELECT Photo_Tag.pid,Photo_Tag.xcoord,Photo_Tag.ycoord,Photo.src_big FROM photo_tag AS Photo_Tag INNER JOIN photo AS Photo ON Photo_Tag.pid = Photo.pid INNER JOIN (SELECT (MAX(pid) * RAND()) AS pid FROM photo_tag WHERE Photo_Tag.subject = {{mystery.uid}}) AS MaxId ON Photo_Tag.pid = MaxId.pid WHERE Photo_Tag.subject =  {{mystery.uid}} ORDER BY Photo_Tag.pid ASC LIMIT 1";

Written to be more readable, that query is:

SELECT Photo_Tag.pid
    ,Photo_Tag.xcoord
    ,Photo_Tag.ycoord
    ,Photo.src_big
FROM photo_tag AS Photo_Tag
INNER JOIN photo AS Photo ON Photo_Tag.pid = Photo.pid
INNER JOIN (
    SELECT (MAX(pid) * RAND()) AS pid 
    FROM photo_tag
    WHERE Photo_Tag.subject = {{mystery.uid}}
) AS MaxId ON Photo_Tag.pid = MaxId.pid
WHERE Photo_Tag.subject = {{mystery.uid}}
ORDER BY Photo_Tag.pid ASC
LIMIT 1;

Using ORDER BY RAND() requires querying every row to build a new "randomness" ID, creating and populating a temporary table with this newly randomized result set, and then querying from that table to bring back your row. For large datasets, this can be very slow.

By instead JOINing the table back onto itself with random pids generated, then ORDERing and LIMITing that dataset, it can result in a much faster query. This article explains in (enormous) detail what I mean, but also shows that a query such as the one above will be faster with a table holding as little as 100 rows, and will be no slower than the original query in any case.

Even if you don't get rid of ORDER BY RAND(), your reduction to a single query should speed up your call.

StackExchange Code Review Q#11427, answer score: 2