If you must, the following will make SELECT COUNT(*) FROM FOO fast for InnoDB:
- Install INSERT and DELETE triggers to maintain the row count for the InnoDB table in a metadata table. This will kill concurrency on the table, just like MyISAM.
- Modify the parser to accept SELECT ESTIMATED_COUNT(*) and evaluate this internally to use the row count estimate provided by the storage engine. Is the estimate good enough? If an exact answer is needed and the table is not locked after the exact value is computed, then the exact value will quickly become incorrect.