php / Mysql associate tree search and item query

Question

I have a tree of categories in my database. I also have a table of items associated with the tree by a category id.

Now, I want to list all items in a specific category and its children and their children, etc...

For now, I proceed this way:

• Retrieve the id of all concerned categories.
• Make a query in the items table with a WHERE clause like this: WHERE cat_id=2 OR cat_id=10 OR ...

I think this way cause the query to be very slow and very long if I have a lot of categories. A search can be in 100 categories sometimes.

Is there a better practice?

Answer 1

From gugl on "storing tree in relational database": http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

• Adjacency List is simple, but not good in most complex cases
• Nested Set is complex from 1st view (mostly during write), but it much more like standard for storing and reading trees in RDBMs.

---

+1 about

EXPLAIN select * from table

that will help you to see bottlenecks.

Also try instead of

column1 = 1 or column1 = 2

something like:

column1 in (1, 2)

But anyway without indexes it wouldn`t help.