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?
