基于YEAR()索引的MySQL Join-列添加或生成的列

Based on the answer https://stackoverflow.com/a/1601812/4050261I am using SQL query as belowFROM workdoneLEFT JOIN staffcost ON YEAR(workdone.date) = staffcost.costyearThe above query does not make use of index which I have on workdone.date column and hence very slow. I have 2 options, i presumeOption 1Add another column workdone.year which is updated through table oncreate and onupdate event. Use this column in the query.Option 2Add Generated (Virtual/Persistent) column workdone.year and then use this column in the query.My Question:Which option is better? From Performance as well as data duplicity standpoint?Should I use Virtual OR Persistent column type?Is there any better alternative?Update 1.1I implemented the solution suggested by OJones, but explain shows me that index was not used. Am I reading the below screenshot incorrectly? 解决方案 Your query is fine as it is. But a query with a LEFT JOIN can only use an index on the right table (staffcost). No index on the left table (workdone) can support the join. So all you need is an index on staffcost(costyear).You can test it with the following script:DROP TABLE IF EXISTS `staffcost`;CREATE TABLE IF NOT EXISTS `staffcost` ( `id` int(10) unsigned NOT NULL, `costyear` year(4) NOT NULL, `data` text COLLATE utf8_unicode_ci, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;INSERT INTO `staffcost` (`id`, `costyear`, `data`) VALUES (1, '2018', '0.6555866465490187'), (2, '2019', '0.12234661925802624'), (3, '2020', '0.64497318737672'), (4, '2021', '0.8578261098431667'), (5, '2022', '0.354211017819318'), (6, '2023', '0.19757679030073508'), (7, '2024', '0.9252509287793663'), (8, '2025', '0.03352430372827156'), (9, '2026', '0.3918687630369037'), (10, '2027', '0.8587709347333489');DROP TABLE IF EXISTS `workdone`;CREATE TABLE IF NOT EXISTS `workdone` ( `id` int(10) unsigned NOT NULL, `date` date NOT NULL, `data` text COLLATE utf8_unicode_ci, PRIMARY KEY (`id`), KEY `date` (`date`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;INSERT INTO `workdone` (`id`, `date`, `data`) VALUES (1, '2017-12-31', '0.40540353712197724'), (2, '2018-01-01', '0.8716141803857071'), (3, '2018-01-02', '0.1418603212962489'), (4, '2018-01-03', '0.09445909605776807'), (5, '2018-01-04', '0.04671454713373868'), (6, '2018-01-05', '0.9501954782290342'), (7, '2018-01-06', '0.6108337804776'), (8, '2018-01-07', '0.2035824984345422'), (9, '2018-01-08', '0.18541118147355615'), (10, '2018-01-09', '0.31630844279779907');EXPLAINSELECT * FROM workdoneLEFT JOIN staffcost ON YEAR(workdone.date) = staffcost.costyear;ALTER TABLE `staffcost` ADD INDEX `costyear` (`costyear`);EXPLAINSELECT * FROM workdoneLEFT JOIN staffcost ON YEAR(workdone.date) = staffcost.costyear;SELECT VERSION();Results:id|select_type|table |type|possible_keys|key|key_len|ref|rows|Extra 1|SIMPLE |workdone |ALL | | | | | 10| 1|SIMPLE |staffcost|ALL | | | | | 10|Using where; Using join buffer (flat, BNL join)id|select_type|table |type|possible_keys|key |key_len|ref |rows|Extra1 |SIMPLE |workdone |ALL | | | | | 10|1 |SIMPLE |staffcost|ref |costyear |costyear|1 |func| 1|Using whereVERSION()10.1.26-MariaDBOnline demo: http://rextester.com/JIAL51740 这篇关于基于YEAR()索引的MySQL Join-列添加或生成的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！