Hello,

I have very simple test table:

ID:AutoInc
Name:String[30]

I also create non-unique case IN-Sensetive index for Name field.

Why simple queries like:

SELECT * FROM TestTable WHERE Name='smith'
SELECT * FROM TestTable WHERE Name LIKE 'jhon%'

Is always UN-OPTIMIZED ??

This queries becomes OPTIMIZED only after altering table and removing
case IN-Sensetive option from Index.

I should avoid using case insensetive indexes ?

What is faster:

1. case sensetive indexes and queries like SELECT * FROM TestTable WHERE LOWER(Name)='smith'
2. case INsensetive indexes and queries like SELECT * FROM TestTable WHERE Name='Smith'

Thanx for help !

Max


I think you need to do

SELECT * FROM TestTable WHERE LOWER(Name)= LOWER('smith')

which lets the engine know what's going on

Roy Lambert

Max

When you use case-insensitive indexes you must follow a simple rule, use
UPPER or LOWER to really use the index otherwise it will be un-optimized.

Eduardo

LOWER() function helps !

Thank you very much !