web programming – Increase the speed of processing requests

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)

Scroll to Top