Customs fileds and filters, sql tables

  • Posts: 210
  • Thank you received: 4
6 years 10 months ago #268792

-- HikaShop version -- : 3.0.1
-- Joomla version -- : 3.7.0
-- PHP version -- : 7.1.0

Hi,
I got about 50 000 products in my data base and i'm going to add more in future.
They are a diffrent group of products and they are have diffrent customs fields to filter data on frontend side

Much products means much customs fileds.

As i see now all customs fields stored in hikashop_product sql table so as i have a lot of this fields i will got a huge sql hikashop_product table
And all product got this fileds but with NULL values - it seems like inconvinient
I think it will be better to make speacial customs fileds table in this case

So are you going to change something in hikashop architecture or engine to solve this case ?

Please Log in or Create an account to join the conversation.

  • Posts: 81361
  • Thank you received: 13037
  • MODERATOR
6 years 10 months ago #268799

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.

Please Log in or Create an account to join the conversation.

Time to create page: 0.053 seconds
Powered by Kunena Forum