I have a very complex search query to write that will touch many tables, have conditions and joins. For efficiency reasons, I am strongly considering creating a "cache table" where I would maybe once a day compute most of the work so that when a user wants to do a search, it would be faster.
However this cache table could easily have 10 to 50 million rows. Is this bad? Is this worth it? Will I gain in efficiency? What are things that I should keep an eye for?
We use InnoDB.