CODEWITHSUNDEEP
phpmysql
Jase
Jase

Reputation: 599

Mysql hierarchical data find by path

Following Managing Hierarchical Data in MySQL, I've implemented the use of hierarchical data just fine, but it's been requested that I add functionality in to retrieve data using a path like a folder structure.

I'm not even sure how to start with doing this.

I've tried exploding the path into individual segments and doing a loop to build up the SQL query to search this way but it's horribly slow.

Am I approaching this correctly and could you please give me any suggestions or links to help, please? I've searched for a couple of weeks and found nothing relating to this, so I'm guessing it's not common practice.

Upvotes: 1

Views: 541

Answers (1)

Elad
Elad

Reputation: 3130

The hierarchical data document that you linked has an implementation based on multiple joins on the same table. If this is too slow, then there is a simple solution that isn't as 'pure' but should work faster:

Add a 'path' column to your table, that stores the path from the root down to the current category as a varchar. Make sure you put an index on that column. For example: 'ELECTRONICS>PORTABLE ELECTRONICS>MP3 PLAYERS>FLASH'

I used '>' as the delimiter between category names - make sure that whatever you use does not appear as part of any of your categories.

Now in order to retrieve a record using the absolute path to it you construct the path with your delimiters and query for an exact match. You can also find entire subtrees with a single fast query by using a LIKE query and supplying the path prefix.

Note that you can also query for relative paths with LIKE queries on path suffix and even do some crazy stuff using regular expressions. But these will be slow. However, you can add more columns - the reverse path for instance (FLASH>MP3 PLAYERS>PORTABLE ELECTRONICS>ELECTRONICS) will allow for fast queries on path suffixes.

Upvotes: 1

Related Questions