Re: Partitioning
Three bulky strings versus one INT/BIGINT: It is a toss-up. Since you plan to do some processing, then reach back into the table to get the strings, that may be the best way to do it. Especially if you need the strings from only some of the rows that.
INT SIGNED can hold +/- 2 billion. (A signed, 4-byte, 32-bit number)
INT UNSIGNED can hold 0..4 billion. (Unsigned, 4-byte, 32-bit number)
BIGINT can hold a huge number (8 bytes)
Three tables versus one: Generally 1 table is better. Finding a single row with a good index will take about the same time, whether it is 2B rows or 9B rows. So there needs to be another reason for splitting into 3 tables.
SELECT START_CM, END_CM, SCORE FROM BOOK_TBL WHERE URI='MC001'
would need INDEX(URI). I assume URI is not unique, and the SELECT might return multiple rows?
SELECT TEXT_1, TEXT_2 FROM BOOK_TBL WHERE URI_CM1='ABxxxxx' AND URI_CM2='EFxxxxx' AND URI='MCxxxxx'
This depends on how big the columns are. TEXT is problematical. VARCHAR has limitations. So, one of these is best:
INDEX(uri, uri_cm1, uri_cm2) -- in place of the previous suggestion, or
INDEX(uri) -- as before.