I'm an MYSQL not-quite-newb but getting the hang of having big data. I have a table with a few million rows, with about 8 columns. I need to select count() across any combination of these columns, and I need to do it fast, for use with AJAX on a frontend.
Since running a basic SELECT is becoming expensive when I have been thinking of something like an aggregate table - which somebody posted about here - http://forums.mysql.com/read.php?32,590663,590663
. But given that I have 8 columns, and each of these columns has at least 3 (in some cases 6) potential values, I'm looking at a LOT of possibilities (something like 400,000). Should I run some kind of aggregate code that loops iteratively through every single possible combination and store that total in the aggregate/rollup table, or is there a better way to do this? Any help, including code and/or pointers to other pages would be tremendously appreciated.
BTW I am using this with PHP but this seemed like the best specialized sub-forum to post this so apologies if it should be put elsewhere