Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Fixed
-
5.5.42
-
None
Description
Noticed in MDEV-6735 (https://mariadb.atlassian.net/browse/MDEV-6735?focusedCommentId=69337&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-69337)
using ftp://ftp.askmonty.org/public/mdev7786.dump.gz test case:
MariaDB [test]> explain SELECT t1.f6, t1.f6, t1.f3, t1.f4, t1.f7, t1.f8, t1.f9, DATE_FORMAT( t2.f3, '%Y-%m-%d' ), t3.f5 FROM t1 LEFT JOIN t2 ON t2.f1 = t1.f1 AND t2.f3 >= '2015-03-14' AND t2.f3 <= '2015-03-14' LEFT JOIN t3 ON t3.f3=MONTH(t2.f3) ORDER BY t2.f3 limit 300;
|
+------+-------------+-------+-------+---------------+------+---------+------+--------+------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+------+---------+------+--------+------------------------------------------------+
|
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 110000 | Using temporary; Using filesort |
|
| 1 | SIMPLE | t2 | range | idx1,idx2 | idx2 | 3 | NULL | 1117 | Range checked for each record (index map: 0x6) |
|
| 1 | SIMPLE | t3 | ref | idx3 | idx3 | 4 | func | 1477 | Using where |
|
+------+-------------+-------+-------+---------------+------+---------+------+--------+------------------------------------------------+
|
3 rows in set (0.00 sec)
|
|
|
MariaDB [test]> explain SELECT t1.f6, t1.f6, t1.f3, t1.f4, t1.f7, t1.f8, t1.f9, DATE_FORMAT( t2.f3, '%Y-%m-%d' ), t3.f5 FROM t1 LEFT JOIN t2 force index(idx2) ON t2.f1 = t1.f1 AND t2.f3 >= '2015-03-14' AND t2.f3 <= '2015-03-14' LEFT JOIN t3 ON t3.f3=MONTH(t2.f3) ORDER BY t2.f3 limit 300;
|
+------+-------------+-------+-------+---------------+------+---------+------+--------+-------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+------+---------+------+--------+-------------------------------------------------+
|
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 110000 | Using temporary; Using filesort |
|
| 1 | SIMPLE | t2 | range | idx2 | idx2 | 3 | NULL | 1117 | Using where; Using join buffer (flat, BNL join) |
|
| 1 | SIMPLE | t3 | ref | idx3 | idx3 | 4 | func | 1477 | Using where |
|
+------+-------------+-------+-------+---------------+------+---------+------+--------+-------------------------------------------------+
|
Even though idx2 was chosen for t2 already, forcing it changed the query plan (in this case for the better but hopefully the MDEV-6735 patch will fix that).
Does method of use on the index need to be specified in the force index?
Attachments
Issue Links
Activity
There is no forcing of any index in the test case for this bug MDEV-6735..
I also can't see any indication of code path changes that corrected the change in query plan based on forcing an index.
https://github.com/MariaDB/server/commit/992d782d784fb960a705a3532562224d16c6a6d0
I've asked that on IRC, the answer was
for FORCE INDEX queries, test_if_quick_select set read_time=DBL_MAX in the beginning
so we needed to check that we didn't produce a quick select whose cost was greater than full table scan cost
Fixed by the fix for
MDEV-6735.