2 ms·
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 can
by fendale 17y ago
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 17y agoYou 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