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?
Advertisement
Answer
The problem is that the query optimizer does a bad job on finding a suitable index when using variables. This is a known issue.
If you use EXPLAIN on both queries, you will see the difference. Just try to avoid variables when not necessary.
For the first query, the optimizer “sees” the chosen values and decides an index can be perfectly used to satisfy the selected range more efficiently.
For the second query, the optimizer is unaware of the two values that define the range, and decides to fall back to a FULL SCAN instead.