How to build a menu tree for the fastest possible delivery?

Question

PHP

We have a table in MySQL with menu items.
Fields to select:
id - id
id_parent - parent
ordering - sorting (who is first)
public - on/off (boolean)
At the moment, menu generation is the slowest module in the system (600ms with 700 menu items in the tree)
Maximum tree nesting level - 3.
What method to store trees for maximum performance? Adjacency List or Nested Set?
Is it possible to cache the tree so that it doesn't regenerate?

Answer 1

That Adjacency List, that Nested Set allows you to build a tree in one request.
You just pull it with sorting by id_parent and ordering and collect it into an associative array, for example, so that you can then unwind it into what you need in the view.
Most importantly, do not do recursive loading with queries to the database, it is possible to make a LOT of queries to the database, which is extremely unproductive.
With regards to caching - also a good option.
The scheme is simple - the tree construction mechanism looks to see if there is a cache - if there is - it returns it, if not - it builds a tree and caches it (banal serialization, for example).
When writing/changing a resource participating in a tree, you can flush the cache so that a subsequent request builds it and adds it to the cache.

Answer 2

Show the code, 700 points is not much.

Answer 3

In general, it is strange that you have such a speed - are you by any chance making requests in a cycle? Immediately one request to the database goes and then everything is built by a nested function. Alternatively, you can store a tree in the session or in some kind of cache.