Workaround in mysql for partial Index or filtered Index?

by

Filtered indexes could be emulated with function indexes and CASE expression(MySQL 8.0.13 and newer):

CREATE TABLE t(id INT PRIMARY KEY, myColumn VARCHAR(100));

-- NULL are not taken into account with `UNIQUE` indexes   
CREATE UNIQUE INDEX myIndex ON t((CASE WHEN myColumn <> 'myText' THEN myColumn END));


-- inserting excluded value twice
INSERT INTO t(id, myColumn) VALUES(1, 'myText'), (2, 'myText');

-- trying to insert different value than excluded twice
INSERT INTO t(id, myColumn) VALUES(3, 'aaaaa');

INSERT INTO t(id, myColumn) VALUES(4, 'aaaaa');
-- Duplicate entry 'aaaaa' for key 'myIndex'

SELECT * FROM t;

db<>fiddle demo

Output:

+-----+----------+
| id  | myColumn |
+-----+----------+
|  1  | myText   |
|  2  | myText   |
|  3  | aaaaa    |
+-----+----------+

More Related Contents:

  1. With MySQL, how can I generate a column containing the record index in a table?
  2. How do I add indexes to MySQL tables?
  3. Differences between INDEX, PRIMARY, UNIQUE, FULLTEXT in MySQL?
  4. When should I use a composite index?
  5. Is the primary key automatically indexed in MySQL?
  6. MySQL indexes – what are the best practices?
  7. Mysql::Error: Specified key was too long; max key length is 1000 bytes
  8. How to see indexes for a database or table in MySQL?
  9. Speeding up row counting in MySQL
  10. What is cardinality in MySQL?
  11. MySQL varchar index length
  12. What’s the difference between using INDEX vs KEY in MySQL?
  13. Mysql Improve Search Performance with wildcards (%%)
  14. How can I manipulate MySQL fulltext search relevance to make one field more ‘valuable’ than another?
  15. DOs and DONTs for Indexes [closed]
  16. MySQL performance: multiple tables vs. index on single table and partitions
  17. Is there any performance gain in indexing a boolean field?
  18. How to disable index in innodb
  19. Higher cardinality column first in an index when involving a range?
  20. Does Order of Fields of Multi-Column Index in MySQL Matter
  21. Create an index on a huge MySQL production table without table locking
  22. MySQL Integer vs DateTime index
  23. Does a UNIQUE constraint automatically create an INDEX on the field(s)?
  24. MySQL 5.0 indexes – Unique vs Non Unique
  25. MySQL distinction between e and é (e acute) – UNIQUE index
  26. MySQL optimizing INSERT speed being slowed down because of indices
  27. Would it help to add index to BIGINT column in MySQL?
  28. MySQL – why not index every field?
  29. MySQL: how to index an “OR” clause
  30. Mysql performance on 6 million row table

Leave a Comment