So I'm developing this tracking application atm, it will run on 2 webservers and it's purpose is to track visitor behaviour.
The data will be collected on Server A which is the master and we decided to use the MYSQL Blackhole engine for that, because it doesn't actually execute the inserts and just writes them to the log file, then replicates one table with the data in real-time to Server B, which is the slave.
On Server B we will have the reporting part of the application which will be used to generate statistics reports, so there will be really 99%+ read operations to create statistics reports, and only now and then an insert/update or delete when a user creates a new campaign for example, or deletes one or updates his username / password etc.
The question arose now which MYSQL engine we should use for the reporting part, but we are not sure which engine supports the fastest reads, MYISAM or INNODB?
Any help would be appreciated :)
PS: I forgot to mention that the reporting database on server B, will collect LOTS of data, so it is entirely possible that we will have to handle 100's of millions of rows of data per year and still need to be able to create statistics reports from this data very quick.
Based on your requirement of mostly reads, I would say MYISAM. Greatly simplifying, use MYISAM for lots of reads, INNODB for lots of writes.
Developer99 has written a little chart to help you choose:
My ISAM InnoDB
Required full text Search Yes
Require Transactions Yes
frequent select queries Yes
frequent insert,update,delete Yes
Row Locking (multi processing on single table) Yes
Relational base design Yes