[fendale]

Well, certainly in Oracle, it can use an index on a like if it is structured:

    where col like 'str%'

However, doing

   where col like '%str'

Means it cannot use any index. So in the example given, it would be able to use an index because there is a leading 'path' before the %, assuming MySQL can do the same thing.

In Oracle you can do hierarchy query's using the 'connect by' syntax, which means you can structure your table with id, parent_id and get all the rows out efficiently in a single piece of SQL, which is nice!

[c0un7d0wn]

You use a database that supports CTE or at the very least, recursive querying options, then you format your schema like so:

  Comments:
  /-----------------------------\
  |   ID   | ParentID |   etc   |
  |-----------------------------|
  |    1   |   Null   |   etc   |
  |    2   |   Null   |   etc   |
  |    3   |     1    |   etc   |
  |    4   |     1    |   etc   |
  |    5   |     4    |   etc   |
  \-----------------------------/

You then use a Common Table Expressions query, like this:

  WITH CommentTree (ParentID, ID, Level)
  AS
  (
    SELECT ParentID, ID, 0 as [Level], 
        FROM Comments
        WHERE ParentID = [ROOT COMMENT ID] OR ParentID IS NULL
    UNION ALL
        SELECT c.ParentID, c.ID, ct.Level + 1
            FROM Comments c
            JOIN CommentTree ct ON ct.ID = c.ParentID
  )
  -- Now actually query the data from the CTE Expression
  SELECT ParentID, ID, Level FROM CommentTree 
     ORDER BY ParentID, Level, ID

This returns a table with a calculated column called Level.

  /---------------------------\
  | ParentID | Level |   ID   |
  |---------------------------|
  |   Null   |   0   |   1    |
  |   Null   |   0   |   2    |
  |    1     |   1   |   3    |
  |    1     |   1   |   4    |
  |    4     |   2   |   5    |
  \---------------------------/

Which of course, would represent this tree:

       Root
      /    \
     2      1
           / \
          3   4
               \
                5