Question:
Good time!
A little about the task:
It is necessary to implement a selection of articles depending on the selected section:
Sections have a tree structure with unlimited nesting, in the database the partition table has id & parent_id fields that implement the entire tree structure.
The articles themselves are bound only to "leaf" sections that do not have subsections.
An example of a section structure:
Section 1
- Section 1.1
- Section 1.1.1 (articles here)
- Section 1.1.2 (here)
- Section 1.2
- Section 1.2.1 (here)
- Section 1.3
- Section 1.3.1 (and here)
Those. if Section 1 is selected, then you need to get the id of Sections 1.1.1, 1.1.2, 1.2.1, 1.3.1 and display all the articles that are linked to them. The problem is how it is implemented: At the beginning, the IDs of the children of Section 1 are selected, then the IDs of their children, a total of 4 requests (in a specific case). But on a real basis, in large sections, up to 100-500 queries. Plus, fetching articles by section_id IN (an array of 100+ elements) is a long task.
Question:
How to increase the speed of work, tk. the page eventually loads in 10-15 seconds.
Answer:
You can do this:
Add the path field to the section table, which will contain the path from the root to the current category.
For example, in your example, each section has id from 1 to 8. Then the path field for Section 1.1.1 will be like this 1-2-3 When you click on Section 1, all id of child sections will be selected with the following query:
SELECT *
FROM table
WHERE path LIKE "$id%";
This way you will only have one request)