i am trying to run a select command from metabase (v0.31.0 on windows server 2012 R2) against a MariaDB (configured as MySQL in the Admin section).
the metabase application is intended for non technical users so running native SQL commands is out of the question.
when building a question using the Custom option and selecting the date column (defined in MariaDB as date) the SQL command that is sent to MariaDB looks like this:
SELECT * FROM gbxdr WHERE date(gbxdr.SeizeDate) = date(now()) LIMIT 2000
the MariaDB takes minutes on minutes to return the answer for this query
running the same command against the MariaDB from sql workbench give the same very poor query result.
when running the same query as
SELECT * FROM gbxdr WHERE (gbxdr.SeizeDate) = date(now()) LIMIT 2000
removed the date before the (gbxdr.SeizeDate) column it takes the MariaDB less than a second to retrieve the data.
is there any option to permanently remove the date statement from the select command ?
I don’t think so but it is a database problem more than Metabase. If you somehow eliminate “Date” statement from the query you most probably encounter some other problems. Do we have an index on the SeizeDate column?
I’m sure I’ve seen this mentioned with other databases too. It’s converting a datetime to a date to do a comparison. As there’s no index on the converted value, it’s slow.
What do you have that column set to in Metabase? Is it a timestamp (ie a datetime) or a date?
after additional digging and consulting with several DBA the problem is clear now
the SeizeDate is defined as date in the DB and it is fully indexed thus running a direct query on this field will give the results very fast (e.g. select * where SeizeDate = date(now))
the metabase adds the function date before the field name thus the index on this field is lost during the select operation since the DB executor will not know the result of this function on the field value and can not search the index values (e.g select * where date(SeizeDate) = date(now))
in the second case the select execution will do an ALL search on the DB and if the DB is huge the execution time will take LONG (!) time.
unfortunately for me most if my searches are based on the date field and if we can not remove the date function before the date fields it will force me to look for other alternatives .