Best use of SQL_CALC_FOUND_ROWS for complex queries
I am trying to populate a paginated web page of database content, using the techniques with SQL_CALC_FOUND_ROWS and LIMIT.
The description given in the online documentation (http://dev.mysql.com/doc/mysql/en/information-functions.html) addresses this for situations where the database query returns exactly one row for each row in the final web page. When my query is more complex, I end up with multiple query result rows for a single primary key. That's fine, but now LIMIT does not really help me any more.
So, I break my task in two. First query is for PK only, so this returns exactly one row for each final table row on my page. I use LIMIT and SELECT FOUND_ROWS() so I can implement pagination correctly. A second query is used that would give me everything I really need to know about each item in my web page, this query involves JOINS and has multiple rows per PK. I extend the query with "WHERE PK IN (7,8,9,10,11,12)" which would give me 6 instances, starting from number 7. That works fine, but I'm unsure if this is the best way to do this. Any feedback appreciated.
One step more complex, the PK is actually a composite key, so now I can no longer use "WHERE PK IN (7,8,9,10,11,12)". Instead, it's going to be something convoluted like
"WHERE ((PKa=val1 AND PKb=val2) OR (PKa=val3 AND PKb=val4) OR (PKa=val5 AND PKb=val6) OR (PKa=val7 AND PKb=val8) OR (PKa=val9 AND PKb=val10) OR (PKa=val11 AND PKb=val12))". I hate this. I'm just not fluent enough in SQL to improve on it. Again, any feedback appreciated.