I am in charge of a DB scalability project that I thought I had under wraps, but just discovered a roadblock in MySQL that prevents my strategy from working. I know it sounds pathetic, but I'm desperate for some guidance on this one. I even paid a DB expert for a day of consulting on this issue and he came up with essentially the same plan that I had. Here is the situation that I have:

 

Consider a library that needs a database to track book checkouts. There is one table currently for all of these, the schema resembles:

 

        section_id, book_id, member_id, checkout_ts, checkin_ts (and a unique constraint is placed across section_id, book_id, member_id, and checkout_ts)

 

A member checks out a book from a particular section at the checkout_ts and when the book is returned, the checkin_ts is populated. Each of these records is held in the library checkout tracking software until the checkin occurs, so all of these fields will always be populated in the database. There are no IDs for these records because of the volume of records there are (potentially 500M+). The original author of this database didn't want the space taken up by the IDs.

 

Because the interface to this database is a web interface, and the usage model is real-time reporting on book checkout statistics, the query time has to be especially low. My plan to tackle this was to partition the table based on the book_id. The benchmarks for this were very positive and this was my plan until about 20 minutes ago. It was then I discovered that there is a partition limit in MySQL of 1024 partitions. There are potentially 10's of thousands of books in the database, so my plan is now shot.

 

I also tried partitioning by the checkin_ts with a range of approximately every 90 days, but the performance wasn't quite there and MySQL complained when I tried to put the unique constraint in place. This constraint is needed because of the risk in library checkout tracking software of messing up and putting duplicate checkouts for a user when a false checkin is entered. Unfortunately, there's not much I can do about that part of it either.

 

So, now I'm left with no ideas whatsoever to address the performance issues on this database. Is there something that I'm missing that is obvious? I'm mediocre at DB design and SQL, but this is beyond my capabilities. Is there a kind soul out there with more knowledge than I that could possibly lend a hand?