Comments on: Using ProxySQL and VIRTUAL Columns to Solve ORM Issues

By: Corrado Pandiani

Corrado Pandiani — Fri, 30 Mar 2018 10:03:40 +0000

In reply to Hugo. Yes, you're right. Inverting columns was for sure an oversight in this case. Consider the post as a general hint instead. A hint on how to cope with similar issues when dealing with ORMs or some legacy software you are not allowed to modify or you don't want to modify for many valid reasons. The primary aim was to outline the proxysql's query rewriting capability. This was a trivial case but there could be more complex cases of queries in which the optimizer can't use the index on the generated column, or even a generated column is not needed at all, but you still have to rewrite the query in order to achieve a better execution plan. Proxysql is a very helpful tool in such cases.

By: Hugo

Hugo — Fri, 30 Mar 2018 06:24:23 +0000

I don’t understand why you use ProxySQL to rewrite the query because the optimizer can directly use the index on the virtual column (but you inverted the two fields):

ALTER TABLE sessions ADD COLUMN sess_delete INT UNSIGNED GENERATED ALWAYS AS ((sess_lifetime + sess_time)) VIRTUAL;

ALTER TABLE sessions ADD INDEX(sess_delete);

mysql> EXPLAIN DELETE FROM sessions WHERE sess_lifetime + sess_time < 1521025847\G
*************************** 1. row ***************************
id: 1
select_type: DELETE
table: sessions
partitions: NULL
type: range
possible_keys: sess_delete
key: sess_delete
key_len: 5
ref: const
rows: 5
filtered: 100.00
Extra: Using where
1 row in set (0.00 sec)

https://dev.mysql.com/doc/refman/5.7/en/generated-column-index-optimizations.html