indexing - Mysql: Prefix index vs index -

can mysql prefix index used normal index?

if there text column , length of prefix index on e.g. 1 , query was:

select * table textcol = 'ab'

would give me rows starting 'a' or check whole column value?

in general, i'm curious know if there's caveats when using prefix indexes. not regarding performance, more if queries have written differently or whether client have logic.

if think moment, mysql still give correct answer, no index... won't fast... so, yes, you'll still correct answer prefix index.

the performance lower because after matching "possible" rows index, server go row data , further filter results against where clause. 2 steps instead of one, nothing application needs care about.

the caveats include fact prefix index won't used optimizer operations, sorting or grouping, because doesn't cover enough of column data purposes.

a prefix index isn't sorted beyond length of prefix. if query uses full index find rows, you'll find rows returned sorted in index order implicitly. if application expects behavior of course expecting should not expect, because order in rows returned undefined unless explicitly order by. don't rely on coincidental behavior, in query, because not rows matched prefix index not in particular order... in fact order of result set ordering not explicit subject change @ time.

and, prefix index can't used covering index. covering index refers case of columns in select happen included in 1 index (plus optionally primary key, since it's there too). optimizer read data directly index, instead of using index identify rows in main table data. if index can't used matching rows, optimizer full scan of covering index, instead of doing full scan of entire table, saving i/o , time. (this capability, way, should enough reason select columns want, instead of lazy select * -- potentially opens more efficient query plans). prefix index can't used this, either.

but aside performance , optimizations , queries implicitly expect (which should not expecting), there no logic-related caveat comes mind prefix index. result still correct.