MySQL Forums :: Performance :: Sorting with limit across a join. How to make it fast?

Hello,
I have two tables, user_books and books. user_book contains books owned by a particular person (uid), and has the following relevant fields:
uid bigint(20) unsigned
bid int(10) unsigned
status tinyint(4)

book contains information for actual books, and has the following relevant fields:
id int(10) unsigned
title varchar(200)
author varchar(100)

bid in user_book is foreign key to id in the book table.
A single person can have upwards of 500+ books, meaning 500 entries in user_book with the same uid. I want to get a subset (say 50) books owned by a person, sorted in one of a couple ways:

status, author, title
author, title, status
title, author, status

The problem here is that "status" is in one table, and "author" and "title" are in the other table. I can't figure out how to get an efficient sort and limit across the two tables. For example, if I do this:

SELECT status, title, author FROM user_book, book WHERE bid=book.id AND uid=123456 ORDER BY status, author, title LIMIT 400, 50;

then it does a filesort and takes 10 seconds or so, and is just as fast as if I brought in all 500+ results, instead of the 50 that I want. I don't think I can create an index across tables (e.g. status, author, title), so how do I make this fast?

Thanks,
Garth



Edited 1 time(s). Last edit at 11/13/2009 05:36PM by Garth Shoemaker.