{"id":2746,"date":"2019-03-14T09:57:19","date_gmt":"2019-03-14T08:57:19","guid":{"rendered":"http:\/\/dasini.net\/blog\/?p=2746"},"modified":"2019-03-14T16:15:16","modified_gmt":"2019-03-14T15:15:16","slug":"mysql-functional-indexes","status":"publish","type":"post","link":"https:\/\/dasini.net\/blog\/2019\/03\/14\/mysql-functional-indexes\/","title":{"rendered":"MySQL Functional Indexes"},"content":{"rendered":"\n<div class=\"wp-block-image\"><figure class=\"aligncenter\"><img data-recalc-dims=\"1\" decoding=\"async\" src=\"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/sunset_by_olivier_dasini_800x348.png\" alt=\"Sunset in Crete by Olivier DASINI\"\/><\/figure><\/div>\n\n\n\n<p>Since MySQL 5.7 one can put indexes on expressions, aka functional indexes, using <a rel=\"noreferrer noopener\" aria-label=\" (opens in a new tab)\" href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/create-table-generated-columns.html\" target=\"_blank\">generated columns<\/a>. Basically you first need to use the generated column to define the functional expression, then indexed this column.<br><br>Quite useful when dealing with JSON functions, you can find an example <a rel=\"noreferrer noopener\" aria-label=\"here (opens in a new tab)\" href=\"http:\/\/dasini.net\/blog\/2015\/11\/30\/json-et-colonnes-generees-avec-mysql\/\" target=\"_blank\">here<\/a> and the documentation <a rel=\"noreferrer noopener\" aria-label=\" (opens in a new tab)\" href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/create-table-secondary-indexes.html#json-column-indirect-index\" target=\"_blank\">there<\/a>.<\/p>\n\n\n\n<p>Starting with MySQL <strong><a rel=\"noreferrer noopener\" aria-label=\" (opens in a new tab)\" href=\"https:\/\/dev.mysql.com\/doc\/relnotes\/mysql\/8.0\/en\/news-8-0-13.html#mysqld-8-0-13-optimizer\" target=\"_blank\">8.0.13<\/a><\/strong> we have now an easiest way to create <a href=\"https:\/\/dev.mysql.com\/worklog\/task\/?id=1075\" target=\"_blank\" rel=\"noreferrer noopener\" aria-label=\"functional indexes (opens in a new tab)\">functional indexes<\/a> (or functional key parts as mentioned in the <a rel=\"noreferrer noopener\" aria-label=\"documentation (opens in a new tab)\" href=\"https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/create-index.html#create-index-functional-key-parts\" target=\"_blank\">documentation<\/a>) \\o\/<\/p>\n\n\n\n<p>Let&rsquo;s see how with a quick practical example.<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>Using <a href=\"https:\/\/dev.mysql.com\/downloads\/mysql\/\" target=\"_blank\" rel=\"noreferrer noopener\" aria-label=\" (opens in a new tab)\">MySQL<\/a> 8.0.15<\/li><li>Querying the <a rel=\"noreferrer noopener\" aria-label=\"test_db (opens in a new tab)\" href=\"https:\/\/github.com\/datacharmer\/test_db\" target=\"_blank\">test_db<\/a> database<\/li><\/ul>\n\n\n\n<p>Below <em>salaries<\/em> table structure:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>mysql> SHOW CREATE TABLE salaries\\G\n*************************** 1. row ***************************\n       Table: salaries\nCreate Table: CREATE TABLE `salaries` (\n  `emp_no` int(11) NOT NULL,\n  `salary` int(11) NOT NULL,\n  `from_date` date NOT NULL,\n  `to_date` date NOT NULL,\n  PRIMARY KEY (`emp_no`,`from_date`),\n  CONSTRAINT `salaries_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees` (`emp_no`) ON DELETE CASCADE\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci\n1 row in set (0,00 sec)<\/code><\/pre>\n\n\n\n<p>It contains some data<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>mysql> SELECT count(*) FROM salaries;\n+----------+\n| count(*) |\n+----------+\n|  2844047 |\n+----------+\n\n\nmysql> SELECT * FROM salaries LIMIT 3;\n+--------+--------+------------+------------+\n| emp_no | salary | from_date  | to_date    |\n+--------+--------+------------+------------+\n|  10001 |  60117 | 1986-06-26 | 1987-06-26 |\n|  10001 |  62102 | 1987-06-26 | 1988-06-25 |\n|  10001 |  66074 | 1988-06-25 | 1989-06-25 |\n+--------+--------+------------+------------+<\/code><\/pre>\n\n\n\n<p>Let&rsquo;s focus on the following query: <br>SELECT * FROM salaries WHERE <strong>YEAR(to_date)=1985<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>mysql> SELECT * FROM salaries WHERE YEAR(to_date)=1985;\n+--------+--------+------------+------------+\n| emp_no | salary | from_date  | to_date    |\n+--------+--------+------------+------------+\n|  14688 |  42041 | 1985-07-06 | 1985-08-08 |\n...snip...\n| 498699 |  40000 | 1985-09-25 | 1985-09-28 |\n+--------+--------+------------+------------+\n89 rows in set (0,80 sec)\n\n\nmysql> explain SELECT * FROM salaries WHERE YEAR(to_date)=1985\\G\n*************************** 1. row ***************************\n           id: 1\n  select_type: SIMPLE\n        table: salaries\n   partitions: NULL\n         type: ALL\npossible_keys: NULL\n          key: NULL\n      key_len: NULL\n          ref: NULL\n         rows: 2838426\n     filtered: 100.00\n        Extra: Using where<\/code><\/pre>\n\n\n\n<p>We have a full table scan (<code><strong>     type: ALL<\/strong><\/code>), meaning no index is used. Perhaps because there is no index on column <em>to_date<\/em>&#8230; \ud83d\ude09<br>So let&rsquo;s add an index on <em>to_date<\/em> !<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>mysql> ALTER TABLE salaries ADD INDEX idx_to_date (to_date);\nQuery OK, 0 rows affected (17,13 sec)\nRecords: 0  Duplicates: 0  Warnings: 0\n\n\nmysql> SHOW CREATE TABLE salaries\\G\n*************************** 1. row ***************************\n       Table: salaries\nCreate Table: CREATE TABLE `salaries` (\n  `emp_no` int(11) NOT NULL,\n  `salary` int(11) NOT NULL,\n  `from_date` date NOT NULL,\n  `to_date` date NOT NULL,\n  PRIMARY KEY (`emp_no`,`from_date`),\n  KEY `idx_to_date` (`to_date`),\n  CONSTRAINT `salaries_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees` (`emp_no`) ON DELETE CASCADE\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci<\/code><\/pre>\n\n\n\n<p>And run again the query with the hope of a better execution plan<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>mysql> explain SELECT * FROM salaries WHERE YEAR(to_date)=1985\\G\n*************************** 1. row ***************************\n           id: 1\n  select_type: SIMPLE\n        table: salaries\n   partitions: NULL\n         type: ALL\npossible_keys: NULL\n          key: NULL\n      key_len: NULL\n          ref: NULL\n         rows: 2838426\n     filtered: 100.00\n        Extra: Using where<\/code><\/pre>\n\n\n\n<p>Ouch! Still have a full table scan !<br>The index can&rsquo;t be used because of the use of a function (<em><a href=\"https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/date-and-time-functions.html#function_year\" target=\"_blank\" rel=\"noreferrer noopener\" aria-label=\"YEAR() (opens in a new tab)\">YEAR()<\/a><\/em>) on the indexed column (<em>to_date<\/em>).<br>BTW if you&rsquo;re really surprise, maybe you should read <a rel=\"noreferrer noopener\" aria-label=\"this (opens in a new tab)\" href=\"https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/optimization-indexes.html\" target=\"_blank\">this<\/a>. \ud83d\ude09<\/p>\n\n\n\n<p>This is the case when you need a functional index!<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>mysql> ALTER TABLE salaries ADD INDEX idx_year_to_date((YEAR(to_date)));\nQuery OK, 0 rows affected (20,04 sec)\nRecords: 0  Duplicates: 0  Warnings: 0<\/code><\/pre>\n\n\n\n<p>The syntax is very similar of the creation of a \u00ab\u00a0regular\u00a0\u00bb index. Although you must be aware of the double parentheses: <strong>((<\/strong> &lt;expression&gt; <strong>))<\/strong> <br>We can now see our new index named <em>idx_year_to_date<\/em> and the indexed expression <em>year(to_date)<\/em> :<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>mysql> SHOW CREATE TABLE salaries\\G\n*************************** 1. row ***************************\n       Table: salaries\nCreate Table: CREATE TABLE `salaries` (\n  `emp_no` int(11) NOT NULL,\n  `salary` int(11) NOT NULL,\n  `from_date` date NOT NULL,\n  `to_date` date NOT NULL,\n  PRIMARY KEY (`emp_no`,`from_date`),\n  KEY `idx_to_date` (`to_date`),\n  KEY `idx_year_to_date` ((year(`to_date`))),\n  CONSTRAINT `salaries_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees` (`emp_no`) ON DELETE CASCADE\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci\n\n\nmysql> SELECT INDEX_NAME, EXPRESSION \nFROM INFORMATION_SCHEMA.STATISTICS \nWHERE TABLE_SCHEMA='employees' \n    AND TABLE_NAME = \"salaries\" \n    AND INDEX_NAME='idx_year_to_date';\n+------------------+-----------------+\n| INDEX_NAME       | EXPRESSION      |\n+------------------+-----------------+\n| idx_year_to_date | year(`to_date`) |\n+------------------+-----------------+<\/code><\/pre>\n\n\n\n<p>Let&rsquo;s test our query again<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>mysql> explain SELECT * FROM salaries WHERE YEAR(to_date)=1985\\G\n*************************** 1. row ***************************\n           id: 1\n  select_type: SIMPLE\n        table: salaries\n   partitions: NULL\n         type: ref\npossible_keys: idx_year_to_date\n          key: idx_year_to_date\n      key_len: 5\n          ref: const\n         rows: 89\n     filtered: 100.00\n        Extra: NULL\n\n\nmysql> SELECT * FROM salaries WHERE YEAR(to_date)=1985;\n+--------+--------+------------+------------+\n| emp_no | salary | from_date  | to_date    |\n+--------+--------+------------+------------+\n|  14688 |  42041 | 1985-07-06 | 1985-08-08 |\n...snip...\n| 498699 |  40000 | 1985-09-25 | 1985-09-28 |\n+--------+--------+------------+------------+\n89 rows in set (0,00 sec)<\/code><\/pre>\n\n\n\n<p>Here we go!<br>Now the query is able to use the index. And in this case we have a positive  impact on the execution time.<\/p>\n\n\n\n<p>It is also interesting to note that it is possible to use <em>idx_to_date<\/em>, the first index created (the non functional one) if we can rewrite the original query:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>mysql> EXPLAIN SELECT * \nFROM salaries \nWHERE to_date BETWEEN '1985-01-01' AND '1985-12-31'\\G\n*************************** 1. row ***************************\n           id: 1\n  select_type: SIMPLE\n        table: salaries\n   partitions: NULL\n         type: range\npossible_keys: idx_to_date\n          key: idx_to_date\n      key_len: 3\n          ref: NULL\n         rows: 89\n     filtered: 100.00\n        Extra: Using index condition\n\n\nmysql> SELECT * \nFROM salaries \nWHERE to_date BETWEEN '1985-01-01' AND '1985-12-31'\n+--------+--------+------------+------------+\n| emp_no | salary | from_date  | to_date    |\n+--------+--------+------------+------------+\n|  20869 |  40000 | 1985-02-17 | 1985-03-01 |\n...snip...\n|  45012 |  66889 | 1985-08-16 | 1985-12-31 |\n+--------+--------+------------+------------+\n89 rows in set (0,00 sec)<\/code><\/pre>\n\n\n\n<p>This saves an index, I mean less indexes to maintain for the engine. Also speaking of maintenance cost, the cost to maintain a functional index is higher than the cost of a regular one. <br><br>In the other side the execution plan is less good (query cost higher) and obviously you must rewrite the query.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Requirements and restrictions.  <\/h3>\n\n\n\n<p>A primary key cannot be a functional index:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>mysql> CREATE TABLE t1 (i INT, PRIMARY KEY ((ABS(i))));\nERROR 3756 (HY000): The primary key cannot be a functional index<\/code><\/pre>\n\n\n\n<p>You can not index non-deterministic functions (RAND(), UNIX_TIMESTAMP(), NOW()\u2026)<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>mysql> CREATE TABLE t1 (i int, KEY ((RAND(i))));\nERROR 3758 (HY000): Expression of functional index 'functional_index' contains a disallowed function.<\/code><\/pre>\n\n\n\n<p>SPATIAL and FULLTEXT indexes cannot have functional key parts.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Conclusion<\/h3>\n\n\n\n<p>Functional index is an interesting and a relevant feature, it could be very useful to optimize your queries without rewrite them and especially when dealing with <a rel=\"noreferrer noopener\" aria-label=\"JSON (opens in a new tab)\" href=\"https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/json.html\" target=\"_blank\">JSON<\/a> documents and other complex types. <\/p>\n\n\n\n<p>Obviously all the details you must know are in the MySQL documentation: <a rel=\"noreferrer noopener\" aria-label=\"Functional Key Parts (opens in a new tab)\" href=\"https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/create-index.html#create-index-functional-key-parts\" target=\"_blank\">Functional Key Parts<\/a><br>If you interested in the <strong>high level architecture<\/strong> and the <strong>low level design<\/strong> please read the <a rel=\"noreferrer noopener\" aria-label=\"workload (opens in a new tab)\" href=\"https:\/\/dev.mysql.com\/worklog\/task\/?id=1075\" target=\"_blank\">workload<\/a>.<\/p>\n\n\n\n<p> <br \\=\"\"> <\/p>\n\n\n\n<p> <br \\=\"\"> <\/p>\n\n\n\n<p><em>Thanks for using MySQL!<\/em><\/p>\n\n\n\n<p>   <\/p>\n\n\n\n<p><a href=\"https:\/\/twitter.com\/freshdaz\" target=\"_blank\" rel=\"noreferrer noopener\">Follow me on twitter<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Since MySQL 5.7 one can put indexes on expressions, aka functional indexes, using generated columns. Basically you first need to use the generated column to define the functional expression, then indexed this column.<\/p>\n<p>Quite useful when dealing with JSON functions, you can find an example here and the documentation there.<\/p>\n<p>Starting with MySQL 8.0.13 we have now an easiest way to create functional indexes (or functional key parts as mentioned in the documentation) \\o\/<\/p>\n<p>Let\u2019s see how with a quick practical example.<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"footnotes":""},"categories":[88,203,34],"tags":[96,191,202],"class_list":["post-2746","post","type-post","status-publish","format-standard","hentry","category-astuce","category-mysql-en","category-optimisation","tag-index","tag-optimization","tag-performance"],"aioseo_notices":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p9LfWW-Ii","jetpack-related-posts":[{"id":1515,"url":"https:\/\/dasini.net\/blog\/2015\/11\/17\/30-mins-with-json-in-mysql\/","url_meta":{"origin":2746,"position":0},"title":"30 mins with JSON in MySQL","author":"Olivier DASINI","date":"17 novembre 2015","format":false,"excerpt":"MySQL 5.7 is GA and has over than 150 new features. One of them is a Native JSON Data Type and JSON Functions: \"Allows for efficient and flexible storage, search and manipulation of schema-less data. Enhancements include a new internal binary format, support for easy integration within SQL, and index\u2026","rel":"","context":"Dans &quot;json&quot;","block_context":{"text":"json","link":"https:\/\/dasini.net\/blog\/category\/json\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":3058,"url":"https:\/\/dasini.net\/blog\/2019\/05\/14\/check-constraints-in-mysql\/","url_meta":{"origin":2746,"position":1},"title":"CHECK constraints in MySQL","author":"Olivier DASINI","date":"14 mai 2019","format":false,"excerpt":"MySQL (really) supports CHECK CONSTRAINT since version 8.0.16. In this article I will show you 2 things: - An elegant way to simulate check constraint in MySQL 5.7 & 8.0. - How easy & convenient it is to use CHECK constraints in 8.0.16.","rel":"","context":"Dans &quot;Astuce&quot;","block_context":{"text":"Astuce","link":"https:\/\/dasini.net\/blog\/category\/astuce\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":8442,"url":"https:\/\/dasini.net\/blog\/2025\/10\/14\/querying-the-unstructured-natural-language-to-sql-for-json-data\/","url_meta":{"origin":2746,"position":2},"title":"Querying the Unstructured: Natural Language to SQL for JSON Data","author":"Olivier DASINI","date":"14 octobre 2025","format":false,"excerpt":"Bridging natural language processing with semi-structured data brings both opportunity and complexity. MySQL HeatWave GenAI\u2019s NL2SQL feature shows how natural language can simplify data interaction \u2014 even for JSON documents. Yet, because JSON embeds both data and metadata within a single column, LLMs may struggle without explicit schema cues. By\u2026","rel":"","context":"Dans &quot;AI&quot;","block_context":{"text":"AI","link":"https:\/\/dasini.net\/blog\/category\/ai\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2025\/10\/Querying-the-Unstructured-Natural-Language-to-SQL-for-JSON-Data-400.png?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":2465,"url":"https:\/\/dasini.net\/blog\/2018\/07\/23\/30-mins-with-mysql-json-functions\/","url_meta":{"origin":2746,"position":3},"title":"30 mins with MySQL JSON functions","author":"Olivier DASINI","date":"23 juillet 2018","format":false,"excerpt":"JSON (JavaScript Object Notation) is a popular way for moving data between various systems, including databases. Starting with 5.7 MySQL implemented a native JSON data type and a set of JSON functions that allows you to perform operations on JSON values.","rel":"","context":"Dans &quot;json&quot;","block_context":{"text":"json","link":"https:\/\/dasini.net\/blog\/category\/json\/"},"img":{"alt_text":"MySQL native JSON data type","src":"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/json_icon.png?resize=350%2C200","width":350,"height":200},"classes":[]},{"id":1578,"url":"https:\/\/dasini.net\/blog\/2016\/02\/25\/30-mins-with-mysql-query-rewriter\/","url_meta":{"origin":2746,"position":4},"title":"30 mins with MySQL Query Rewriter","author":"Olivier DASINI","date":"25 f\u00e9vrier 2016","format":false,"excerpt":"Sometime DBAs have to deal with problematic queries and cannot tackle the problem at the source (problematic queries from ORMs, third party apps,... or source unknown...). MySQL 5.7 provides a pre and post parse query rewrite APIs where users can write their own plug-ins. With the post-parse query plugin, you\u2026","rel":"","context":"Dans &quot;MySQL&quot;","block_context":{"text":"MySQL","link":"https:\/\/dasini.net\/blog\/category\/mysql-en\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":2874,"url":"https:\/\/dasini.net\/blog\/2019\/04\/02\/mysql-json-document-store\/","url_meta":{"origin":2746,"position":5},"title":"MySQL JSON Document Store","author":"Olivier DASINI","date":"2 avril 2019","format":false,"excerpt":"MySQL 8.0 provides another way to handle JSON documents, actually in a \"Not only SQL\" (NoSQL) approach... In other words, if you need\/want to manage JSON documents (collections) in a non-relational manner, with CRUD (acronym for Create\/Read\/Update\/Delete) operations then you can use MySQL 8.0! Did you know that?","rel":"","context":"Dans &quot;Document Store&quot;","block_context":{"text":"Document Store","link":"https:\/\/dasini.net\/blog\/category\/document-store\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/2746","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/comments?post=2746"}],"version-history":[{"count":32,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/2746\/revisions"}],"predecessor-version":[{"id":3122,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/2746\/revisions\/3122"}],"wp:attachment":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/media?parent=2746"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/categories?post=2746"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/tags?post=2746"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}