Select problem toward MariaDB

1

Hello
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.
but
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 ?

thanks.

2

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?

3

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?

4

It sounds like this performance issue, which lists different databases:

5

Hello
the SeizeDate in the MariaDB database is indexed as BTREE

image

as i mentioned when running the select in workbench with the date option it runs for a very long time (minutes) and the same select without the date runs for a few seconds.

first line is with the date option and the second one is without

image
image1098×62 3.82 KB

What do you have that column set to in Metabase? Is it a timestamp (ie a datetime) or a date? - i am not sure what that means because in metabase the SiezeDate is defined like all other fields

image
image1588×123 5.42 KB

i did not see anywhere an option to define the type of the field.

thanks.

6

Hello
found out what you meant regarding the metabase field type and it is set to date

image
image882×133 3.95 KB

7

Hello
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))
but
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 .

thanks.