Hi guys, i have a table like this:
CREATE TABLE mov_boleto (
cc_hash_key VARCHAR(75) NOT NULL DEFAULT '',
linha_digitavel VARCHAR(54) NOT NULL DEFAULT '',
PRIMARY KEY (cc_hash_key),
INDEX linha_digitavel (linha_digitavel)
)

with 500k rows
i need to search part of 'linha_digitavel' field (the left part):
LEFT(linha_digitavel,37), but i'm having a problem....

check the query

SELECT cc_hash_key
FROM mov_boleto WHERE
left(linha_digitavel,37) IN ("34191.09008 27471.621287 27580.420001","34191.09008 27471.701287 27580.422001");

the problem is the optimization...
"Using where", 521069 rows

could it use the index linha_digitavel? like the "LIKE" operator?
SELECT cc_hash_key
FROM mov_boleto WHERE
linha_digitavel like "34191.09008 27471.621287 27580.420001%" OR
linha_digitavel like "34191.09008 27471.621287 27580.422001%" OR
linha_digitavel ="34191.09008 27471.621287 27580.420001" OR
linha_digitavel ="34191.09008 27471.621287 27580.422001"

explain=> "Using index condition; Using where", rows =2

could SUBSTRING(char field,1,?) and LEFT(char_field,?)
FUNCTIONS be optimized for search?

--------
i'm thinking about a query rewrite in sql_select.cc or opt_range.cc, i think that opt_range.cc is better
but we can optimize ENUM too when MDEV-4419 is done, in this case sql_select.cc is a better place to optimize...

the point is, optimize rewrinting this:

LEFT(column,1234) = "some string"
or
SUBSTR(column,1,1234) = "some string"

to

(LEFT(column,1234) = "some string" AND column LIKE "some string%")
or
(SUBSTR(column,1,1234) = "some string" AND column LIKE "some string%")

and this:

LEFT(column,1234) IN ('string1','string2','string3')

to

(LEFT(column,1234) IN ('string1','string2','string3') AND 
(
  LEFT(column,1234) LIKE 'string1%' OR 
  LEFT(column,1234) LIKE 'string2%' OR 
  LEFT(column,1234) LIKE 'string3%'
)
)

or rewrite the LEFT(column,1234) to a virtual column, if it's exists, in this case we can use index