Customs fileds and filters, sql tables

Hi,

Thank you for your input.
We don't plan on changing that.
We're ok with the trade off of having many columns on the product table. It indeed can make the table big when you have a lot of products and a lot of custom product fields, but even in that case, I'm not sure adding a table to store the custom fields will help speed things up as it will add big JOINs between the tables when you want to filter the products on these custom fields (which is the whole point of having custom product fields).
I think that there is a much easier way and efficient to improve the database :
Make sure that in your custom product fields, you try as much as possible to have integers as values with the text as title.
That way, in the database, only the integer number is saved instead of text.
Then, in your hikashop_product table, in your database, change the type of these columns to unsigned integer (on even smaller integer types like smallint). You can even add indexes on some of the columns to speed things even further.
That will reduce the size of the table a lot since storing an integer way better SQL-wise, and it will also help the filtering queries a lot as conditions on a integer column are much much faster than on a longtext column.
Another way you can improve things is to have generic custom fields, so instead of having custom fields like : custom_product_field_for_products_in_category_car and custom_product_field_for_products_in_category_bike, you could have a joint custom field which would be used for both types of products.
When you configure filters on the custom fields, you can have the filter configured to be dynamic and limited to one category.
So you could create two filters on the same custom field for different categories and have it display different filtering values with a different name. The only issue you're left with there is to change the label of the custom field on the product page to adapt it based on the category but that's only a simple PHP modification in the view. Doing that can reduce a lot the amount of columns you have.

With both tricks, that's already a lot you can do without going into complex coding while still keeping the simple architecture we have.