MySQL Join based on YEAR() indexing - Column add or Generated Column [英] MySQL Join based on YEAR () indexing - Column add or Generated Column

问题描述

基于答案

解决方案

您的查询没有问题.但是带有 LEFT JOIN 的查询只能使用右表上的索引 (staffcost).左表 (workdone) 上没有索引可以支持连接.因此，您只需要一个关于 staffcost(costyear) 的索引.

您可以使用以下脚本对其进行测试:

删除表如果存在`staffcost`;如果不存在 `staffcost` 则创建表(`id` int(10) 无符号非空，`costyear` year(4) NOT NULL,`数据`文本整理utf8_unicode_ci，主键(`id`)) 引擎=InnoDB 默认字符集=utf8 排序=utf8_unicode_ci;插入staffcost"(id"、costyear"、data")值(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');如果存在 `w​​orkdone` 则删除表；如果不存在 `w​​orkdone` 则创建表(`id` int(10) 无符号非空，`date` 日期不为空，`数据`文本整理utf8_unicode_ci，主键(`id`)，键`日期`(`日期`)) 引擎=InnoDB 默认字符集=utf8 排序=utf8_unicode_ci;插入`workdone`(`id`，`date`，`data`)值(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');解释SELECT * FROM 已完成LEFT JOIN staffcost ON YEAR(workdone.date) = staffcost.costyear;ALTER TABLE `staffcost` ADD INDEX `costyear` (`costyear`);解释SELECT * FROM 已完成LEFT JOIN staffcost ON YEAR(workdone.date) = staffcost.costyear;选择版本()；

结果:

id|select_type|table |type|possible_keys|key|key_len|ref|rows|Extra1|简单 |工作完成 |全部 |||||10|1|简单|人员成本|全部|||||10|在哪里使用；使用连接缓冲区(平面，BNL 连接)id|select_type|table |type|possible_keys|key |key_len|ref |rows|Extra1 |简单 |工作完成 |全部 |||||10|1 |简单 |staffcost|ref |costyear |costyear|1 |func|1|在哪里使用版本()10.1.26-MariaDB

在线演示: http://rextester.com/JIAL51740

Based on the answer https://stackoverflow.com/a/1601812/4050261

I am using SQL query as below

FROM workdone
LEFT JOIN staffcost ON YEAR(workdone.date) = staffcost.costyear

The above query does not make use of index which I have on workdone.date column and hence very slow. I have 2 options, i presume

Option 1

Add another column workdone.year which is updated through table oncreate and onupdate event. Use this column in the query.

Option 2

Add Generated (Virtual/Persistent) column workdone.year and then use this column in the query.

My Question:

1. Which option is better? From Performance as well as data duplicity standpoint?
2. Should I use Virtual OR Persistent column type?
3. Is there any better alternative?

---

Update 1.1

I 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');

EXPLAIN
SELECT * FROM workdone
LEFT JOIN staffcost ON YEAR(workdone.date) = staffcost.costyear;

ALTER TABLE `staffcost` ADD INDEX `costyear` (`costyear`);

EXPLAIN
SELECT * FROM workdone
LEFT 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|Extra
1 |SIMPLE     |workdone |ALL |             |        |       |    |  10|
1 |SIMPLE     |staffcost|ref |costyear     |costyear|1      |func|   1|Using where

VERSION()
10.1.26-MariaDB

Online demo: http://rextester.com/JIAL51740

这篇关于MySQL Join based on YEAR() indexing - Column add or Generated Column的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！