For Sybase the optimizer determines index usage when the query is run the first time. So if the first request only has a trailing '%' and the column has an index it will be used. A subsequent query with a leading '%' won't be incorrect - the optimizer detects the leading '%' and while it uses the index it will look at all the values for a match.

However, if the first query has a leading '%' then you will get a table scan each time.

I ran the following:

my $sth = $dbh->prepare("select * from market where commodity like ?")
+;

$sth->execute("SPM%");
while(my $d = $sth->fetch) {
    ;
}

$sth->execute("%XXX%");
while(my $d = $sth->fetch) {
    ;
}
[download]

Table: market scan count 1, logical reads: (regular=111 apf=0 total=11
+1), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
[download]

Table: market scan count 1, logical reads: (regular=51889 apf=0 total=
+51889), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
[download]

FWIW :-)

Michael