1

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?

Marm
  • 863
  • 2
  • 15
  • 30
  • 1
    I would say first is too look at the index and maybe run an explain on the query (just put explain in before the select and look at the result). Maybe even post a light version of the table (couple of rows) and the query you are using right now. – stunti May 09 '11 at 15:08
  • How do you store your tree in the db? – piotrm May 09 '11 at 15:25
  • Please provide your actual SQL statement for people to look at. If you havent yet done it, try it. Databases are very fast. – Toby Allen May 09 '11 at 16:10
  • Without knowing more about your data, not possible to guide you, see this question for options: http://stackoverflow.com/questions/4048151/what-are-the-options-for-storing-hierarchical-data-in-a-relational-database – orangepips May 10 '11 at 10:32

1 Answers1

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.

gaRex
  • 4,144
  • 25
  • 37