0

I have a MySQL table that stores hierarchical data using the MPTT method. The table also has a column in which I store a short string identifier (slug) for each row.

I'd like to be able to query the table to find the correct row when given a path comprised of these slugs. I.e. given the path foo/bar I'd like to find the row it represents in the most efficient way possible.

Can I do this in a single SQL query? If not what would be the most efficient way to string together a number of queries to get the correct result? I'm using PHP in my app, which may be relevant if this can't be done purely with SQL queries.

Mark Perkins
  • 260
  • 2
  • 10

1 Answers1

0

If you want speedy reads and don't mind slower writes, cache the paths in another column. You can make regenerating the path caches more efficient by only updating the changed node (and its children) using its parent's cached path.

Walf
  • 8,535
  • 2
  • 44
  • 59