MySQL complex subquery formulation

Question

I have two tables - books and images. books has columns like id, name, releasedate, purchasecount. images has bookid (which is same as the id in books, basically one book can have multiple images. Although I haven't set any foreign key constraint), bucketid, poster (each record points to an image file in a certain bucket, for a certain bookid).

Table schema:

1. poster is unique in images, hence it is a primary key.
2. Covering index on books: (name, id, releasedate)
3. Covering index on images: (bookid,poster,bucketid)

My query is, given a name, find the top ten books (sorted by number of purchasecount) from the books table whose name matches that name, and for that book, return any (preferably the first) record (bucketid and poster) from the images table.

Obviously this can be solved by two queries by running the first, and using its results to query the images table, but that will be slow, so I want to use 'join' and subquery to do it in one go. However, what I am trying is not giving me correct results:

select books.id,books.name,year(releasedate),purchasecount,bucketid,poster from books 
inner join (select bucketid,bookid, poster from images) t on 
t.bookid  = books.id  where name like "%foo%" order by purchasecount desc limit 2;

Can anybody suggest an optimal query to fetch the result set as desired here (including any suggestion to change the table schema to improve search time) ?

Updated fiddle: http://sqlfiddle.com/#!9/17c5a8/1.

The example query should return two results - fooe and fool, and one (any of the multiple posters corresponding to each book) poster for each result. However I am not getting correct results. Expected:

fooe - 1973 - 459 - 11 - swt (or fooe - 1973 - 459 - 11 - pqr)

fool - 1963 - 456 - 12 - xxx (or fool - 1963 - 456 - 111 - qwe)

Answer 1

I agree with Strawberry about the schema. We can discuss ideas for better performance and all that. But here is my take on how to solve this after a few chats and changes to the question.

Note below the data changes to deal with various boundary conditions which include books with no images in that table, and tie-breaks. Tie-breaks meaning using the max(upvotes). The OP changed the question a few times and added a new column in the images table.

Modified quetion became return 1 row make per book. Scratch that, always 1 row per book even if there are no images. The image info to return would be the one with max upvotes.

Books table

create table books 
(   id int primary key, 
    name varchar(1000), 
    releasedate date, 
    purchasecount int
) ENGINE=InnoDB;

insert into books values(1,"fool","1963-12-18",456);
insert into books values(2,"foo","1933-12-18",11);
insert into books values(3,"fooherty","1943-12-18",77);
insert into books values(4,"eoo","1953-12-18",678);
insert into books values(5,"fooe","1973-12-18",459);
insert into books values(6,"qoo","1983-12-18",500);

Data Changes from original question.

Mainly the new upvotes column.

The below includes a tie-break row added.

create table images 
(   bookid int, 
    poster varchar(150) primary key, 
    bucketid int, 
    upvotes int -- a new column introduced by OP
) ENGINE=InnoDB;

insert into images values (1,"xxx",12,27);
insert into images values (5,"pqr",11,0);
insert into images values (5,"swt",11,100);
insert into images values (2,"yyy",77,65);
insert into images values (1,"qwe",111,69);
insert into images values (1,"blah_blah_tie_break",111,69);
insert into images values (3,"qwqqe",14,81);
insert into images values (1,"qqawe",8,45);
insert into images values (2,"z",81,79);

Visualization of a Derived Table

This is just to assist in visualizing an inner piece of the final query. It demonstrates the gotcha for tie-break situations, thus the rownum variable. That variable is reset to 1 each time the bookid changes otherwise it increments. In the end (our final query) we only want rownum=1 rows so that max 1 row is returned per book (if any).

Final Query

select b.id,b.purchasecount,xDerivedImages2.poster,xDerivedImages2.bucketid
from books b
left join
(   select i.bookid,i.poster,i.bucketid,i.upvotes,
    @rn := if(@lastbookid = i.bookid, @rn + 1, 1) as rownum,
    @lastbookid := i.bookid as dummy
    from 
    (   select bookid,max(upvotes) as maxup
        from images
        group by bookid
    ) xDerivedImages
    join images i
    on i.bookid=xDerivedImages.bookid and i.upvotes=xDerivedImages.maxup
    cross join (select @rn:=0,@lastbookid:=-1) params
    order by i.bookid
) xDerivedImages2
on xDerivedImages2.bookid=b.id and xDerivedImages2.rownum=1
order by b.purchasecount desc
limit 10

Results

+----+---------------+---------------------+----------+
| id | purchasecount | poster              | bucketid |
+----+---------------+---------------------+----------+
|  4 |           678 | NULL                |     NULL |
|  6 |           500 | NULL                |     NULL |
|  5 |           459 | swt                 |       11 |
|  1 |           456 | blah_blah_tie_break |      111 |
|  3 |            77 | qwqqe               |       14 |
|  2 |            11 | z                   |       81 |
+----+---------------+---------------------+----------+

The significance of the cross join is merely to introduce and set starting values for 2 variables. That is all.

The results are the top ten books in descending order of purchasecount with the info from images if it exists (otherwise NULL) for the most upvoted image. The image selected honors tie-break rules picking the first one as mentioned above in the Visualization section with rownum.

Final Thoughts

I leave it to the OP to wedge in the appropriate where clause at the end as the sample data given had no useful book name to search on. That part is trivial. Oh, and do something about the schema for the large width of your primary keys. But that is off-topic at the moment.