2

Using MySQL with a very large table (millions of entries), I need to find a row according to the 6 rightmost letters in one of its (indexed) fields.

I've tried using: SELECT * FROM 'users' WHERE field LIKE '%abcdef'

and SELECT * FROM 'users' WHERE RIGHT(field,6) = 'abcdef'

Both queries take about 8.5 seconds to complete. For comparison, should I use the = operator with the whole of the field, it'll take 0.15 seconds.

The only idea I have in mind right now is to create an additional indexed column in the table containing the 6 rightmost letters of the field, and use the = operator against it.

Any more elegant suggestions will be highly appreciated.

Thanks :)

Miki
  • 419
  • 1
  • 4
  • 15

1 Answers1

4

The only idea I have in mind right now is to create an additional indexed column in the table containing the 6 rightmost letters of the field, and use the = operator against it.

This is exactly the way to do it in MySQL.

Unfortunately MySQL doesn't yet support indexes on expressions or indexed views, so you have to do it the way you suggested.

Related

Community
  • 1
  • 1
Mark Byers
  • 811,555
  • 193
  • 1,581
  • 1,452