JP <sqlamigo-/[EMAIL PROTECTED]> wrote:
Thanks all. Actually I was just looking for the position of a single
name. Based on your feedback, this one works to get the results:
SELECT count(*) FROM clients WHERE name<'foo';
but its performance is directly proportional to the position of the
name in the table. For example, searching for Zach takes longer than
searching for Abigail. It seems it is not using any index, but rather
doing a record by record sweep on the 'count'.
If it were not using any index but performed a direct scan of the table,
then it would have had to examine every record every time regardless of
the string it were searching for. Thus the time would be proportional to
the number of records in the table, and independent of the parameter.
Most likely the query uses an index on name, and has to scan the index
until the name is encountered to count up the number of records, so the
time is proportional to the position of the parameter in the index.
Given an index stored as a B-tree, I can't think of any algorithm to do
it any faster than O(N) worst case.
Igor Tandetnik
