Slow sql statement when using variables

Question

I have the following SQL statement running against a MariaDB 10.1.26 with ~2.000 rows with instant results.

select value, datetime from Schuppen 
where (value = (select min(value) from Schuppen where (measure = 'temp') 
    and datetime between '2018-11-01 00:00:00' and '2018-11-02 00:00:00')) 
and datetime between '2018-11-01 00:00:00' and '2018-11-02 00:00:00';

When I use the following statement with variables for the datetime fields, the execution takes ~5.5 seconds.

set @startdate = cast('2018-11-01 00:00:00' as datetime);
set @enddate = cast('2018-11-02 00:00:00' as datetime);
select value, datetime from Schuppen 
where (value = (select min(value) from Schuppen where (measure = 'temp') 
    and datetime between @startdate and @enddate)) 
and datetime between @startdate and @enddate;

The more data rows I have, the longer it takes to execute the statement. Seems like the variables change the behaviour of the statement somehow.

What's wrong here?

Answer 1

I use MySQL Workbench and @variables are very useful to query/search different tables for a given attribute. I ran into a similar issue. After scouring through different threads and trying different things, it worked well when I set the @variable to be of exactly the same type and same encoding as the column in the table(s) that I am searching for that variable.

For example:

SET @keyword = CONVERT(CAST("KEYWORD" AS CHAR(8)) USING ASCII);

In this case, the search column cname in my table customer is of type CHAR(8) and encoded using ASCII:

SELECT * FROM CUSTOMERS WHERE cname=@keyword;

If you have multiple tables to query, where cname is CHAR(10) in one and CHAR(8) in another, then you can do the following:

SET @keyword = "KEYWORD";
SELECT * FROM CUSTOMERS WHERE cname=CONVERT(CAST(@keyword AS CHAR(8)) USING ASCII);
SELECT * FROM EMPLOYEES WHERE cname=CONVERT(CAST(@keyword AS CHAR(10)) USING ASCII);