index by END of string, not beginning of string

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
My understand is that MYSQL only supports indexes on the beginning of a
string of data.

for example, if you have a list of email addresses, searching for
something like '' would need to do a full table scan.

why doesn't mysql offer a feature where it indexes by the END of the
string, so we can do these types of searches efficiently?

an index for email addresses of length 3 would only contain the last 3
chars, but it would still be a useful index type.


Re: index by END of string, not beginning of string

dt wrote:
Quoted text here. Click to load it

Probably the same answer as for any other "why don't they offer feature
X?"  The answer is, "great idea! we'll do it when it comes to the top of
the priority list."

By the way, what you're describing could be achieved with current MySQL:
create a second column, copying the string to it using the REVERSE()
function, and then indexing that column.

Another extension to MySQL could be to allow us to create indexes based
on an expression, like REVERSE(colname), and then any expression in a
query that uses that exact syntax could make use of the index.  This is
called "expression indexes" and it's an implementation feature in some
other RDBMS products (e.g. Firebird).

Bill K.

Site Timeline