Hello everyone,
I have a little problem while coding an e-commerce site, built on symfony3.
Here is an overview of my used table on mysql-side:
https://picload.org/image/rdcdwoig/db_example.png
As shown here, there is only one table where i am selecting the data from. No annoying joins between multiple tables which may slow down speed.
Here is a little mockup to show the actual status quo of what i want to render:
https://picload.org/image/rdcdwoid/wireframe.jpg
The Usecase is to get a filtered product view (for example "give me all products where color is green"). Then you get all products listet (red area) + on sidebar the possibility to set further filters for filtering stuff like the brand or size (yellow area; in practical there are even some more filter to use).
The Problem now is, as you see, each result i have to use another adjusted query (for the sidebar different "GROUP BY" statements) with always the same subquery which represents the actual selected productcollection.
This is slowing down my site extremely heavy (as there are over 1 million products in the database as i need about 7-8 times the same query with a little bit different parameters every time).
I found the solution that it would be better to switch to MariaDB, so it would be possible to cache all the subqueries. That is not possible on the actual environment i am working on. MySQL caching is activated here.
Does anyone can give me some hints what i can do to minimize the the queries or mainly speed up the whole process when generating the data for the view? Is there any better solution as I do actually? I guess so..
Many thanks in advance for your help and ideas!
