[Maria-developers] Review for MDEV-8320 Allow index usage for DATE(datetime_column) = const
![](https://secure.gravatar.com/avatar/68ff00c1752bf8aede68a05f77ebd28b.jpg?s=120&d=mm&r=g)
Hello Sergey, I generally like your patch for MDEV-8320: http://lists.askmonty.org/pipermail/commits/2017-November/011680.html However, there is one problem with it. We're changing the code to have data type plugins. Every data type plugin will provide its own list of SQL functions. Those data type specific functions should be able to use the same optimization approach without having to change the server code. Therefore, everything related to YEAR(field) transformation should reside inside Item_func_year, everything related to DATE(field) transformation should reside inside Item_date_typecast. I reorganized the code to implement this idea. Note, also I changed the logic behind DATE(indexed_date_field) transformation slightly. The DATE() just gets removed. DATE(indexed_date_field) = const is changed to: indexed_date_field = const instead of: indexed_date_field BETWEEN ... AND ... Please have a look: sargable_date_cond.result is slightly different in my version. Greetings.
![](https://secure.gravatar.com/avatar/68ff00c1752bf8aede68a05f77ebd28b.jpg?s=120&d=mm&r=g)
Forgot to mention. We'll discussed this on slack already, writing for records anyway. There is no a need for Item_func_{day|year}_{start|end}. You can create Item_{date|datetime}_literal instead. Optimization now works only if the second argument in the comparison is a basic constant. So it's OK to call get_date() for it during fix_fields() time. On 11/30/2017 04:19 PM, Alexander Barkov wrote:
Hello Sergey,
I generally like your patch for MDEV-8320:
http://lists.askmonty.org/pipermail/commits/2017-November/011680.html
However, there is one problem with it.
We're changing the code to have data type plugins.
Every data type plugin will provide its own list of SQL functions. Those data type specific functions should be able to use the same optimization approach without having to change the server code.
Therefore, everything related to YEAR(field) transformation should reside inside Item_func_year, everything related to DATE(field) transformation should reside inside Item_date_typecast.
I reorganized the code to implement this idea.
Note, also I changed the logic behind DATE(indexed_date_field) transformation slightly. The DATE() just gets removed.
DATE(indexed_date_field) = const
is changed to:
indexed_date_field = const
instead of:
indexed_date_field BETWEEN ... AND ...
Please have a look: sargable_date_cond.result is slightly different in my version.
Greetings.
participants (1)
-
Alexander Barkov