Is it possible to have function-based index in MySQL?

by

No, not in a general sense, I don’t believe even 5.6 (the latest version when this answer was first written) has this functionality. It’s worth noting that 8.0.13 and above now support functional indexes, allowing you to achieve what you need without the trigger method described below.

If you are running an older version of mysql, it is possible to only use the leading part of a column (this functionality has been around for a long time), but not one starting at the second or subsequent characters, or any other more complex function.

For example, the following creates an index using the first five characters of a name:

create index name_first_five on cust_table (name(5));

For more complex expressions, you can achieve a similar effect by having another column with the indexable data in it, then using insert/update triggers to ensure it’s populated correctly.

Other than the wasted space for redundant data, that’s pretty much the same thing.

And, although it technically violates 3NF, that’s mitigated by the use of triggers to keep the data in sync (this is something that’s often done for added performance).

More Related Contents:

  1. How do I add indexes to MySQL tables?
  2. Differences between INDEX, PRIMARY, UNIQUE, FULLTEXT in MySQL?
  3. When should I use a composite index?
  4. How do MySQL indexes work?
  5. Best type of indexing when there is LIKE clause [duplicate]
  6. Does MySQL index foreign key columns automatically?
  7. What does the KEY keyword mean?
  8. Is the primary key automatically indexed in MySQL?
  9. Difference between Key, Primary Key, Unique Key and Index in MySQL
  10. MySQL indexes – what are the best practices?
  11. How to see indexes for a database or table in MySQL?
  12. Speeding up row counting in MySQL
  13. What is cardinality in MySQL?
  14. What’s the difference between using INDEX vs KEY in MySQL?
  15. How do I get MySQL to use an INDEX for view query?
  16. DOs and DONTs for Indexes [closed]
  17. MySQL performance: multiple tables vs. index on single table and partitions
  18. Mysql covering vs composite vs column index
  19. Is there any performance gain in indexing a boolean field?
  20. Does Order of Fields of Multi-Column Index in MySQL Matter
  21. MySQL load data infile – acceleration?
  22. Create an index on a huge MySQL production table without table locking
  23. MySQL Integer vs DateTime index
  24. Can MySQL use multiple indexes for a single query?
  25. How to fix MySql: index column size too large (Laravel migrate)
  26. MySQL distinction between e and é (e acute) – UNIQUE index
  27. MySQL optimizing INSERT speed being slowed down because of indices
  28. Would it help to add index to BIGINT column in MySQL?
  29. MySQL: how to index an “OR” clause
  30. Mysql performance on 6 million row table

Leave a Comment