How to optimize the number of requests and organize data storage?

Question

MySQL

If very briefly. There is a small own CRM system (accounting for goods, etc.) on the basis of this system, a module was written (some similarity for CMS), so that the content manager would manage the site directly from CRM. Everything is written in php.

The database structure (in mySQL) is built in such a way that there is a page for the site. And this page has groups of properties (I describe it just approximately for an example, so as not to go into a detailed structure):

• Table with a hierarchical structure of the pages themselves - pages
  
• Table with property groups - props_group (which has an association with pages)
  
• Table with property values ​​- props_value (which has an association with props_group)
  

Out on the site. To access the data of one page - it turns out there are 3 queries to the database. And the system gives all the data on one page (sorting through all its groups and then sorting through the properties of this group). On the pages with a list of products (for example, 24 products are displayed), it turns out that each card makes its 3 queries to the database, a total of 72 requests (3 requests for 24 products). And that's just for the product page. For other content on any page that is on the site (menus, banners, offers, some kind of selection in the footer, etc.). This is all conditional, I write as an example.

To reduce the number of queries to the database. I decided to redo the following way. To the pages table itself, when editing it, all data on its group and property values ​​are saved (i.e., the entire data array from the props_group and props_value tables as a JSON string). But the data size is large and you have to use the MEDIUMTEXT field type . I ended up with one query for each product, but the table immediately grew in size and the query takes correspondingly longer to complete.

Question: How to properly optimize the number of queries to mySQL (or how to properly organize data storage)?

Or how true is this approach to storing data in video json strings if this data is static and changes only when editing the page itself? Does it make sense to look towards redis to store this json result there with all the properties of the page?

Answer 1

It is necessary to divide the formation of the page into stages - getting a list of blocks - getting data for blocks (here we group by block types and for each we remake data getting into a mass one through where id in (...)) and return an associative array with the key = id. Well, rendering cards with the output of already received data. Thus, to form N product cards, you will still need three requests.

Answer 2

1. Request to pages - selection of the necessary data, selection of the ID of the results.
2. Request to props_group WHERE page_id IN (), extracting the ID of the results.
3. Query props_value WHERE props_group_id IN ().
4. Gathering the received information into a pile and transferring this pile to rendering with a template.
0. And no JSON.

Answer 3

That's right - do not start an entity-value system anywhere, do you really have new data types appearing there and changing every day?
Store the values ​​​​as expected - in tables and columns
, and that's not all
wrong in the cycle to interrogate the cards and make one request for each. you have a lot of objects of the same type on the page, you take a list of their id and load all the necessary data at once with one request.
if you don’t want to remake your props_value, add a column with the card ID to this table, then the backend will be able to load all the necessary values ​​\u200b\u200bat once with one request, and already in memory you can quickly collect anything from them