If I understand the MySQL query cache correctly, it functions on a table level. So if I have Tables X, Y, and Z, and MySQL has cached queries on all three, then as soon as Table X is updated in any way (insert, update, or delete), the cached queries for Table X are all invalidated, but tables Y and Z still have their valid cached queries.
If that’s the case, then would it be better to split one big table into multiple, smaller tables? For example, let’s assume I have a blog with a 1,000,000 comments over 1,000 blog posts. All of those comments are in the same Table X, but everytime someone adds a comment, all of the cached query results are invalidated.
So if I divided the system so that Table X is split into 50 tables (each table containing all the comments for 20 blog posts), would the query cache then be more effective by allowing more of the cached queries to stay valid longer (by spreading the comments out over multiple tables)?