CODEWITHSUNDEEP
phpmysqltreehierarchical-datahierarchical
Marm
Marm

Reputation: 873

php / Mysql associate tree search and item query

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:

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?

Upvotes: 1

Views: 361

Answers (1)

gaRex
gaRex

Reputation: 4215

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.

Upvotes: 1

Related Questions