Please keep in mind what goes into the InnoDB Buffer Pool
- 16KB Data Pages for Tables that have been accessed
- 16KB Index Pages for Indexes that have been accessed
- Changes to Secondary Indexes (could take up to 50% of buffer pool in a high-write envrironment)
MySQL's idea of a JOIN
Believe it or not, whether you have one big table and process a WHERE clause or you have joins on multiples, the query processing has the same mechanical behavior. (I wrote about this a year ago : Is there an execution difference between a JOIN condition and a WHERE condition?) You can also find this described in the MySQL Documentation.
You said you fears JOINs. MySQL treats every query like a JOIN. The book I quoted in my post calls a
SELECT on a single table a degenerate join. Multiple tables just uses a little extra resources (file handles, temp tables).
Effects on the InnoDB Buffer Pool
A single table with all needed fields would be loaded into the buffer pool and may produce, at most, 1 temp table for gathering data satisfying a where clause. On the flipside of things, having a single table would mean denormalized data and introducing redundant data into the buffer pool as well as the physical table storage.
Doing joins may actually result in creating multiple tables. This would also load data and associated indexes into the buffer pool. Any subsequent accessing of data might actually take place in the temp tables. Accessing the Buffer Pool would no longer be necessary.
While a large buffer pool helps in the initial processing of a query, tuning the per-connection settings would be more of a help. The parameters I have in mind are
Any query that causes need more space than what there setting are may trigger a paging of that buffer to disk. There is where a slowdown of a query would become manifest and felt.
For the InnoDB Buffer Pool, your main objective should be to have the data and index pages present as often as possible. Avoid have a single denormalized table or you may bloat the buffer pool.
For the query, you may have to run smaller queries if you fear joins. Conversely, you could tune your queries and setup better indexes.
If you are using MySQL 5.6, I would further suggest flushing the map of the InnoDB buffer pool to disk by running this
SET GLOBAL innodb_buffer_pool_dump_now = 1;
Add this to
and restart mysql.
Doing this will have all data and index pages availableon each restart of mysql. This gives every query you have the luxury of having data and indexes in RAM and up-to-date.