Mariadb querying is slow in a table of 2.6M records with indexes

TLDR: I have a table with a lot of records and my queries are slow. I have indexes, but still the query takes a long time to execute. Need help figuring out what the problem is, or atleast a pointer in the right direction. Long story below:- I have a table of products with roughly 2.6M records and the size is set to grow. The table is de normalized and has around 100 odd columns to handle attributes of products objects. Products are categorized with a category name. Each category can have around 5-20 attribute columns. I am using MariaDB 11.0.2 running on Ubuntu on a 4 CPU core linode instance with 8GB Memory Each record stores its own attributes in respective columns and while querying my code picks the right columns for the attributes that are being sorted or filtered. I kept the table de-normalized to avoid large joins and to speed up sorting and filtering. I need to filter and sort and filter based on a lot of these columns, sometimes at the same time. I tried indexes with single key and multiple keys and was able to get speed improvements in certain cases. When multiple columns are being used, multicolumn indexes are used, but when columns of different indexes are used, the indexes are not used a full table scan is done. I tried to force an index but still the query took around 20 seconds. For example when trying to count number of products meeting two where conditions take around 9 seconds to execute There is a limit of 16 keys per index and a keys size of 3075 bytes (IIRC). If I try to filter or sort across keys from multiple indexes, the operation resorts to a full table scan and the whole operation takes around 20s to complete. Can the mentioned limits be increased using configuration entries? There are no frequently accessed columns to create a multicolumn index just on those columns. Almost all columns are equally possible. For more context, the table hosts electronic products each with about 20 or more attributes. I know Mysql is capable of handling 100M records, please point me in the right direction. The schema of the table is given below.

MariaDB [productsdb]> desc products;

https://pastebin.com/GAedK5kq I have the following indexes on the table as well. The indexes starting with name 'abcd' was created for testing and will not be used in production.

MariaDB [productsdb]> show indexes from products;

https://pastebin.com/TLHuShMZ I tried to issue a select query from products table using multiple columns in where clause.

MariaDB [productsdb]> SELECT count(products.id) FROM products WHERE (products.main_category_id = 439 and ( attr_3 in ('400mW','250mW') ) and ( attr_9 in ('7') ));
+------------------------+
| count(products.id) |
+------------------------+
|                    190 |
+------------------------+
1 row in set (8.949 sec)

With regards to the above query I have indices on main_category_id, attr_3, attr_9 and id columns. I do not have a multi column index comprising of these columns. It is faster for this specific query if I have a multi column index containing all these columns. But I have so many attributes, how will I be able to do all permutation combination?