Possibly the biggest challenge for me right now is creating a practical read only database to run along side of the main database.
Master/Slave replication is ruled out.
Thus far, I know how to compile, install, configure, run, and initialize a read only database that would run along side the master.
The problems that keep this from being practical branch alone two paths.
1. staying in sync
once i make the full backup of the master, that's it. from that moment in time onward, the RODB will be out of sync
solutions involve manually creating binary log files with FLUSH MASTER LOGS and replaying those all part of a cron script at some hour
2. CREATE TABLE and ALTER TABLE commands break software
i suppose if i were to do an ALTER TABLE, i would have to immediately apply it to the RODB if the statement would break SELECT queries
any CREATE TABLE statements for new features would be completely broken on the RODB. these statements would need to be applied to the RODB prior to committing the software updates
it's been a long time analyzing why some SELECT queries end up getting slowed down an average of 6 to 8 seconds.
answer is, UPDATEs and INSERTs on the character table can LOCK SELECTs for roughly that amount of time.
i'm also looking at making use of HASH indexes where they would be ultimately superior to binary trees
of course, all of this discussion involves playing by the rules of MySQL. as i write my own replace for MySQLD, i write my own rules.