Mysql / PHP application 99% reads 1% insert/update - What MYSQL Engine To Use?

Question

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.

Answer 1

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

Answer 2

Based on your tables relationship. MyISAM has historically been viewed as faster than InnoDB, but for recent versions of InnoDB, that is true for a much, much smaller set of use cases. MyISAM is typically faster for table scans of read-only tables.