0

I am facing huge problem with our existing category listing. One of our client has 65,000 nested category.

First Level is limited to 10 only.

Cat A
  Cat B
     Cat B_a
     Cat B_b
     Cat B_c
     Cat B_d
  Cat C
  Cat D
  ....
Cat AA
  Cat B
  Cat C
  Cat D
  ....
Cat AAA 
......

Existing code is generating a array with these kind of SQL

SELECT * 
  FROM kl_document_user_label 
  WHERE 
    customer_id='xxx' 
    and position RLIKE '^([0-9]+>){1,1}$' 
    AND ( user_crerate='0' OR user_crerate='1') 
  ORDER BY 
    ulable_name

In Case of 65,000 nested category it hanged.

Sampson
  • 265,109
  • 74
  • 539
  • 565
Dharm Shankar
  • 53
  • 2
  • 6
  • 2
    MySQL doesn't support recursive functions, so it is well suited to neither this path enumeration nor the adjacency list model of storing hierarchical data. You ought to consider restructuring your data to use either nested sets or closure tables. See [this answer](http://stackoverflow.com/a/192462/623041) for more information. – eggyal May 26 '12 at 05:32

1 Answers1

0

When you have 5 levels you can use a left join to connect the same table with the parent_id continuously together. Personally I don't think you need a nested set maybe a database with recursive function can be useful if your application support it.

Micromega
  • 12,486
  • 7
  • 35
  • 72