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

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

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