The Ultimate Guide to High Availability with MariaDB

Sargable DATE and YEAR

You are viewing an old version of this article. View the current version here.

Starting from MariaDB 11.1, conditions in form 

YEAR(indexed_date_col) CMP const_value
DATE(indexed_date_col) CMP const_value

are sargable, provided that

  • CMP is any of =, <=>, <, <=, >, >= .
  • indexed_date_col has a type of DATE, DATETIME or TIMESTAMP and is a part of some index.

Sargable means that the optimizer is able to use such conditions to construct access methods, estimate their selectivity, or perform partition pruning

h2. Implementation

Internally, the optimizer rewrites the condition to an equivalent condition which doesn't use Attachment 'YEAR' not found or Attachment 'DATE' not found function. For example

YEAR(date_col)=2023

is rewritten into 

date_col between '2023-01-01' and '2023-12-31'

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.