Mysql generated virtual column speed question

Factual Questions
1

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:



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:



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!

2

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

3

I’ll give that a try.

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


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.

4

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

5

Glad to hear it worked.

6

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.