--- In firebird-support@yahoogroups.com, chand0s
<chand0s.lazy.inbox@...> wrote:

>
> I have a test table like this -
> create table test_table(name varchar(32), id integer);
> where id field is primary key
>
> and I have a simple SP -
> create procedure test_sp()
> returns(name varchar(32), id integer)
> as
> begin
> for select name, id from test_table into :name, :id
> do suspend;
> end
>
> Question -
> if I will execute my SP with query
> select * from test_sp() order by id
>
> Will SP use indexes?

No, see Dimitry's post for details.

If you want to use an index on ID, you can do it from within the
stored procedure.

eg:

create procedure test_sp()
returns(name varchar(32), id integer)
as
begin
for select name, id from test_table order by ID into :name, :id
do suspend;
end

combined with:

select * from test_sp()

Will return the records ordered by ID possibly using the index on ID
if the optimiser thinks that is beneficial.

Just a note about sorting on indexed fields. It is often slower to
use an index to sort a recordset than to read the records in the
order they occur on disk and sort in memory. Sorting using an index
is extremely beneficial when you are selecting the first n.

Adam