Details
Minor
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?
Gliffy Diagrams
Attachments
Issue Links
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
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.