You haven't mentioned your usage of the database. You've mentioned what sort of information you'd be placing in the database, but not how you'd be retrieving the data.

If you will typically not be accessing the bio, then it would make sense to store the TEXT field seperately. If you have the extra disk space, you can always use a materialized view to join the two tables, or to create a sub table without the TEXT field. Whatever you do, make sure that the TEXT field is the last field in the table. (you also typically want to place VARCHARs, or any other variable length fields to be at the end)

As for what's the percentage of reads to writes that makes an index break even, that's another item with no simple answer -- if the reads involve 25% of the records, then the index is most likely never beneficial. You might also have more than one index on a table, so there would be a different point of breaking even on a table with 8 indexes vs. a table with only one index.

It's also possible that the type of tables might be a factor. I have more experience with Oracle than mySQL, but Oracle uses indexes for record level locking in transactions ... I have no idea if innoDB tables do something similar.

So, I'll go back to my past comments about tuning -- no one's situation is the same as someone else's. Different amounts of memory or disk contension may change how things behave. Your best bet is to build a prototype, and try to load it how you think it will be in production. If it doesn't meet your requirements, then spend time tuning it. Every so often, you'll want to see if your load has change, and retune.

For MyISAM tables, if you don't have any variable-length columns (VARCHAR, TEXT, or BLOB columns), a fixed-size record format is used. This is faster but unfortunately may waste some space. See Section 14.1.3, “MyISAM Table Storage Formats”. You can hint that you want to have fixed length rows even if you have VARCHAR columns with the CREATE option ROW_FORMAT=fixed.

SELECT table1.*, table2.* FROM table1
LEFT JOIN table2 ON table1.textid = table2.textid
WHERE table1.userid = 1
[download]

A materialized view is essentially a table that's built dynamically. In Oracle, there's a command to do it directly, but I do something similar in mySQL and just rebuild the table as needed. (typically through a cron job that fires each morning, as I deal a lot in data warehouse type systems, which mysql works rather well for) In mySQL you need to specify the view directly, whereas Oracle will do query rewriting if it realizes it can.

As for getting fields from two tables, yes, you just have to tell mysql how to join them. Here's an example of creating a view, (plain old view, which is basically just a way to alias the table joins and field lists)

CREATE VIEW myview AS
SELECT a.field1, a.field2, a.field3, b.*
FROM tablea a LEFT JOIN tableb b USING (id);
[download]

I'd suggest seeing the mysql documentation for:

• Create View
• Join Syntax

There is a complex set of rules about trying to update directly through a view though... depending on your type of join, the database may not be able to figure out which record it actually needs to place the value, and you can end up with odd results. For what was described in the original post, however, it should be possible to update through a view to join the bibliography data, and everything else. (because it's a one-to-one relationship, and not a one-to-many)