virtuemart_product_customfields 1.3 million rows (optimize?)

[EvanGR]

Our website (50k products) has over 1.3 million rows in the virtuemart_product_customfields table.

We are having slow performance(*) when browsing products in general, is this something that could be contributing to it?
(* front end requests, CSS/JS cache and minifications etc... are already optimized)

The table format is MyISAM, and collation is utf8_general_ci.
Perhaps convert to InnoDB?
Should we create any indexes?

How can we completely stop custom_fields processing to quickly test performance impact?

Any other thoughts?

Thank you

(Latest Joomla 3.9, VM 3.6.3 10177, php 7.2)

[Studio 42]

It depend how you use the customfields, you should inspect the queries.
Maybe an index problem.
Is your server a shared one or a poor dedicate server ?
How much user you have per day ?
Do you use some cache ?
If you use a filter based on the customfields this can really slowdown your website.
Have you some DDOS attacks ?
You can have many cause. Hard to know why, without inspecting everything.

[EvanGR]

Thanks for your reply.

1) How do I inspect the generated queries? (vmDebug doesn't seem to show them)

2) It's a shared server, but we are looking into moving to VPS.

3) Traffic is almost non existent, it's a new site.

4) We use the Joomla cache functions.

5) We use Custom Filters by breakdesigns, and rely on them heavily. Each product has 3-5 minimum custom fields (we use VM custom fields for now)... and there are 50k+ products...
This is definitely something that we are looking to optimize (performance-wise), but we are still trying to figure out where the main bottlenecks are.

Thanks

[Studio 42]

You can see the queries on activate debug in Joomla config.

[PRO]

How can we completely stop custom_fields processing to quickly test performance impact?

1)Backup the site.

2) BACKUP THE DATABASE,
3) export the product customfields table. Make sure your options are right "truncate table before insert etc. "

4)empty the product customfields table.

test.

Then you can import them back.