How to speed up counting and filtering on table with a lot of columns and filter conditions?
I feel your pain, nik. We have a similar use case here, and have been struggling through as best we can with a metric ton of indexes.

I know you said you need to stick with SQL, but I really think you might want to consider a column store (https://en.wikipedia.org/wiki/Column-oriented_DBMS), perhaps one SQL-like enough for you to still find workable.

MariaDB seems to be embracing this now too:
https://mariadb.com/resources/blog/why-columnstore-important