select first 1 col1 from tab1 where col1 = "A" or col1 = "AB" or col1 = "ABC" or col1 = "ABCD" or col1 = "ABCDE" or col1 = "ABCDEF" or col1 = "ABCDEFG" or col1 = "ABCDEFGH" or col1 = "ABCDEFGHI" order by length(col1) desc
First of all, what is going on here?
There is a table, called tab1 (all names have been changed to protect the innocent) that has a char(n) field named col1 that holds some strings of varying lengths. The SQL is trying to find the row in tab1 with the col1 field that is the longest substring of “ABCDEFGHI”.
There is a unique index on col1 and the query runs fast enough, but this SQL is being run a lot in a tight loop for many different strings and the total ru…