# Mysql generated virtual column speed question

**URL:** <https://boards.straightdope.com/t/mysql-generated-virtual-column-speed-question/849367>\
**Category:** Factual Questions\
**Created:** [March 12, 2020, 9:28pm UTC](https://boards.straightdope.com/t/mysql-generated-virtual-column-speed-question/849367 "2020-03-12T21:28:34Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![Folacin](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/folacin/32/3195_2.png) [@Folacin](https://boards.straightdope.com/u/Folacin)\
**Post date:** [March 12, 2020, 9:28pm UTC](https://boards.straightdope.com/t/mysql-generated-virtual-column-speed-question/849367/1 "2020-03-12T21:28:34Z")

</div>

I have a Mysql DB with a table that contains a virtual column:

alter table my\_table add column foo int(10) unsigned GENERATED ALWAYS AS (if(json\_valid(`detail`),json\_unquote(json\_extract(`detail`,’$.task.escoreusid’)),NULL)) VIRTUAL

If I query against just that table, the response time is reasonable. Something like:

```auto

select m.foo, m.indexedValue, o.otherField from my_table m 
join other_table o on o.indexedValue = m.indexedValue

```

returns 16 rows in 24 seconds.

If I drop the generated field from the query:

```auto

select m.indexedValue, o.otherField from my_table m 
join other_table o on o.indexedValue = m.indexedValue

```

It returns 16 rows in 1.4 seconds.

My assumption is that MySql is being stupid, and in the case where it is returning the generated value it generates it for all rows in the table before applying any where logic.

Does anyone know if there is some way to tell MySql to not do the generation until it has generated its return set? Note that if I do queries returning ‘foo’ with just my\_table (no join) it returns the data quickly, so there are times when it knows to do the virtual generation post select.

thanks!

---

<div class="post-metadata">

**Author:** ![RaftPeople](https://avatars.discourse-cdn.com/v4/letter/r/6f9a4e/32.png) [@RaftPeople](https://boards.straightdope.com/u/RaftPeople)\
**Post date:** [March 12, 2020, 10:12pm UTC](https://boards.straightdope.com/t/mysql-generated-virtual-column-speed-question/849367/2 "2020-03-12T22:12:20Z")

</div>

Assuming that your assumption about the problems is correct (and not knowing mysql generated columns), could you put the generated column in a view, do your query as a subquery, then join to view with gen column

---

<div class="post-metadata">

**Author:** ![Folacin](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/folacin/32/3195_2.png) [@Folacin](https://boards.straightdope.com/u/Folacin)\
**Post date:** [March 13, 2020, 12:58am UTC](https://boards.straightdope.com/t/mysql-generated-virtual-column-speed-question/849367/3 "2020-03-13T00:58:04Z")

</div>

> [@RaftPeople](#):
>
> Assuming that your assumption about the problems is correct (and not knowing mysql generated columns), could you put the generated column in a view, do your query as a subquery, then join to view with gen column

I’ll give that a try.

Might actually be able to do something like this (thanks for the subquery idea):

```auto

select m.foo, m.indexedValue, sub.otherField from my_table m 
join (m.indexedValue, o.otherField from my_table m
join other_table o on o.indexedValue = m.indexedValue
  where <some selection criteria>) as sub
 on m.indexedValue = sub.indexedValue

```

On the theory that Mysql should be smart enough to only generate for the set of rows coming out of the sub-query.

---

<div class="post-metadata">

**Author:** ![Folacin](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/folacin/32/3195_2.png) [@Folacin](https://boards.straightdope.com/u/Folacin)\
**Post date:** [March 13, 2020, 12:41pm UTC](https://boards.straightdope.com/t/mysql-generated-virtual-column-speed-question/849367/4 "2020-03-13T12:41:39Z")

</div>

The subquery appears to solve it - thanks for the help.

---

<div class="post-metadata">

**Author:** ![RaftPeople](https://avatars.discourse-cdn.com/v4/letter/r/6f9a4e/32.png) [@RaftPeople](https://boards.straightdope.com/u/RaftPeople)\
**Post date:** [March 13, 2020, 4:46pm UTC](https://boards.straightdope.com/t/mysql-generated-virtual-column-speed-question/849367/5 "2020-03-13T16:46:00Z")

</div>

Glad to hear it worked.

---

<div class="post-metadata">

**Author:** ![Folacin](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/folacin/32/3195_2.png) [@Folacin](https://boards.straightdope.com/u/Folacin)\
**Post date:** [March 16, 2020, 5:32pm UTC](https://boards.straightdope.com/t/mysql-generated-virtual-column-speed-question/849367/6 "2020-03-16T17:32:20Z")

</div>

Ultimately ended up converting the field to ‘stored’ from ‘virtual’ (project lead didn’t want to mess with changing the query (which was much more involved than shown above)). Not a lot of updates to the data and the stored field is small, so it seems a good solution.

Still curious as to if there is a way to get Mysql to not generate a virtual field of a joined table until the very end (post select), but it is an academic exercise now.