summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/distinct.result8
-rw-r--r--mysql-test/r/endspace.result2
-rw-r--r--mysql-test/r/group_by.result2
-rw-r--r--mysql-test/r/group_min_max.result10
-rw-r--r--mysql-test/r/innodb.result2
-rw-r--r--mysql-test/r/innodb_mysql.result22
-rw-r--r--mysql-test/r/merge.result2
-rw-r--r--mysql-test/r/order_by.result55
-rw-r--r--mysql-test/r/select_found.result2
-rw-r--r--mysql-test/r/subselect.result2
-rw-r--r--mysql-test/t/distinct.test2
-rw-r--r--mysql-test/t/order_by.test37
-rw-r--r--sql/sql_select.cc308
-rw-r--r--sql/sql_select.h6
14 files changed, 357 insertions, 103 deletions
diff --git a/mysql-test/r/distinct.result b/mysql-test/r/distinct.result
index 002dbc6ccb5..795d8956a08 100644
--- a/mysql-test/r/distinct.result
+++ b/mysql-test/r/distinct.result
@@ -209,16 +209,16 @@ id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 index NULL PRIMARY 4 NULL 4 Using index
explain SELECT distinct t1.a from t1 order by a desc limit 1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index NULL PRIMARY 4 NULL 4 Using index
+1 SIMPLE t1 index NULL PRIMARY 4 NULL 1 Using index
explain SELECT distinct a from t3 order by a desc limit 2;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t3 index NULL a 5 NULL 204 Using index
+1 SIMPLE t3 index NULL a 5 NULL 40 Using index
explain SELECT distinct a,b from t3 order by a+1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t3 ALL NULL NULL NULL NULL 204 Using temporary; Using filesort
-explain SELECT distinct a,b from t3 order by a limit 10;
+explain SELECT distinct a,b from t3 order by a limit 2;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t3 index NULL a 5 NULL 204 Using temporary
+1 SIMPLE t3 index NULL a 5 NULL 2 Using temporary
explain SELECT a,b from t3 group by a,b order by a+1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t3 ALL NULL NULL NULL NULL 204 Using temporary; Using filesort
diff --git a/mysql-test/r/endspace.result b/mysql-test/r/endspace.result
index 6fb33dee826..9c8d12362c4 100644
--- a/mysql-test/r/endspace.result
+++ b/mysql-test/r/endspace.result
@@ -154,7 +154,7 @@ teststring
teststring
explain select * from t1 order by text1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index NULL key1 34 NULL 3
+1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort
alter table t1 modify text1 char(32) binary not null;
select * from t1 order by text1;
text1
diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result
index ebe59331357..e09c66ac362 100644
--- a/mysql-test/r/group_by.result
+++ b/mysql-test/r/group_by.result
@@ -1144,7 +1144,7 @@ CREATE TABLE t2 (a INT, b INT, KEY(a));
INSERT INTO t2 VALUES (1, 1), (2, 2), (3,3), (4,4);
EXPLAIN SELECT a, SUM(b) FROM t2 GROUP BY a LIMIT 2;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 index NULL a 5 NULL 4
+1 SIMPLE t2 index NULL a 5 NULL 2
EXPLAIN SELECT a, SUM(b) FROM t2 IGNORE INDEX (a) GROUP BY a LIMIT 2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 ALL NULL NULL NULL NULL 4 Using temporary; Using filesort
diff --git a/mysql-test/r/group_min_max.result b/mysql-test/r/group_min_max.result
index b6bf7260dc2..02b1459afd0 100644
--- a/mysql-test/r/group_min_max.result
+++ b/mysql-test/r/group_min_max.result
@@ -1963,20 +1963,20 @@ id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 128 Using temporary; Using filesort
explain select a1,a2,count(a2) from t1 group by a1,a2,b;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index NULL idx_t1_1 163 NULL 128 Using index
+1 SIMPLE t1 index NULL idx_t1_2 147 NULL 128 Using index
explain extended select a1,a2,count(a2) from t1 where (a1 > 'a') group by a1,a2,b;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t1 index idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 163 NULL 128 75.00 Using where; Using index
+1 SIMPLE t1 index idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_2 147 NULL 128 75.00 Using where; Using index
Warnings:
Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,count(`test`.`t1`.`a2`) AS `count(a2)` from `test`.`t1` where (`test`.`t1`.`a1` > _latin1'a') group by `test`.`t1`.`a1`,`test`.`t1`.`a2`,`test`.`t1`.`b`
explain extended select sum(ord(a1)) from t1 where (a1 > 'a') group by a1,a2,b;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t1 index idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 163 NULL 128 75.00 Using where; Using index
+1 SIMPLE t1 index idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_2 147 NULL 128 75.00 Using where; Using index
Warnings:
Note 1003 select sum(ord(`test`.`t1`.`a1`)) AS `sum(ord(a1))` from `test`.`t1` where (`test`.`t1`.`a1` > _latin1'a') group by `test`.`t1`.`a1`,`test`.`t1`.`a2`,`test`.`t1`.`b`
explain select distinct(a1) from t1 where ord(a2) = 98;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index NULL idx_t1_1 163 NULL 128 Using where; Using index
+1 SIMPLE t1 index NULL idx_t1_2 147 NULL 128 Using where; Using index
select distinct(a1) from t1 where ord(a2) = 98;
a1
a
@@ -2256,7 +2256,7 @@ EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE
a IN (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1_outer index NULL a 10 NULL 15 Using where; Using index
-2 DEPENDENT SUBQUERY t1 index NULL a 10 NULL 15 Using index
+2 DEPENDENT SUBQUERY t1 index NULL a 10 NULL 1 Using index
EXPLAIN SELECT 1 FROM t1 AS t1_outer GROUP BY a HAVING
a > (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2);
id select_type table type possible_keys key key_len ref rows Extra
diff --git a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result
index 804c4b81c17..dce5e1a9a35 100644
--- a/mysql-test/r/innodb.result
+++ b/mysql-test/r/innodb.result
@@ -947,7 +947,7 @@ id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 index NULL PRIMARY 4 NULL #
explain select * from t1 order by b;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index NULL b 4 NULL #
+1 SIMPLE t1 ALL NULL NULL NULL NULL # Using filesort
explain select * from t1 order by c;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL # Using filesort
diff --git a/mysql-test/r/innodb_mysql.result b/mysql-test/r/innodb_mysql.result
index 273374d016b..bf12c31326a 100644
--- a/mysql-test/r/innodb_mysql.result
+++ b/mysql-test/r/innodb_mysql.result
@@ -851,13 +851,13 @@ EXPLAIN SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY a;
id 1
select_type SIMPLE
table t1
-type range
+type index
possible_keys bkey
-key bkey
-key_len 5
+key PRIMARY
+key_len 4
ref NULL
-rows 16
-Extra Using where; Using index; Using filesort
+rows 32
+Extra Using where; Using index
SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY a;
a b
1 2
@@ -946,13 +946,13 @@ EXPLAIN SELECT * FROM t2 WHERE b=1 ORDER BY a;
id 1
select_type SIMPLE
table t2
-type ref
+type index
possible_keys bkey
-key bkey
-key_len 5
-ref const
-rows 8
-Extra Using where; Using index; Using filesort
+key PRIMARY
+key_len 4
+ref NULL
+rows 16
+Extra Using where; Using index
SELECT * FROM t2 WHERE b=1 ORDER BY a;
a b c
1 1 1
diff --git a/mysql-test/r/merge.result b/mysql-test/r/merge.result
index bb4fc654b38..5aa4288500c 100644
--- a/mysql-test/r/merge.result
+++ b/mysql-test/r/merge.result
@@ -86,7 +86,7 @@ a b
19 Testing
explain select a from t3 order by a desc limit 10;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t3 index NULL a 4 NULL 1131 Using index
+1 SIMPLE t3 index NULL a 4 NULL 10 Using index
select a from t3 order by a desc limit 10;
a
699
diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result
index 25fbeadf21b..fd3fb89b5f4 100644
--- a/mysql-test/r/order_by.result
+++ b/mysql-test/r/order_by.result
@@ -1073,3 +1073,58 @@ id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 const PRIMARY,b b 5 const 1
1 SIMPLE t2 ref a a 5 const 2 Using where; Using index
DROP TABLE t1,t2;
+CREATE TABLE t1(
+id int auto_increment PRIMARY KEY, c2 int, c3 int, INDEX k2(c2), INDEX k3(c3));
+INSERT INTO t1 (c2,c3) VALUES
+(31,34),(35,38),(34,31),(32,35),(31,39),
+(11,14),(15,18),(14,11),(12,15),(11,19);
+INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1;
+INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1;
+INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1;
+INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1;
+INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1;
+INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1;
+INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1;
+INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1;
+INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1;
+INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1;
+INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1;
+INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1;
+SELECT COUNT(*) FROM t1;
+COUNT(*)
+40960
+EXPLAIN SELECT id,c3 FROM t1 WHERE c2=11 ORDER BY c3 LIMIT 20;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index k2 k3 5 NULL 88 Using where
+EXPLAIN SELECT id,c3 FROM t1 WHERE c2=11 ORDER BY c3 LIMIT 100;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref k2 k2 5 const 9300 Using where; Using filesort
+EXPLAIN SELECT id,c3 FROM t1 WHERE c2 BETWEEN 10 AND 12 ORDER BY c3 LIMIT 100;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index k2 k3 5 NULL 316 Using where
+EXPLAIN SELECT id,c3 FROM t1 WHERE c2 BETWEEN 10 AND 12 ORDER BY c3 LIMIT 2000;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range k2 k2 5 NULL 12937 Using where; Using filesort
+SELECT id,c3 FROM t1 WHERE c2=11 ORDER BY c3 LIMIT 20;
+id c3
+6 14
+16 14
+26 14
+36 14
+46 14
+56 14
+66 14
+76 14
+86 14
+96 14
+106 14
+116 14
+126 14
+136 14
+146 14
+156 14
+166 14
+176 14
+186 14
+196 14
+DROP TABLE t1;
diff --git a/mysql-test/r/select_found.result b/mysql-test/r/select_found.result
index 7abd65beb46..7896f8a9f4e 100644
--- a/mysql-test/r/select_found.result
+++ b/mysql-test/r/select_found.result
@@ -84,7 +84,7 @@ UNIQUE KEY e_n (email,name)
EXPLAIN SELECT SQL_CALC_FOUND_ROWS DISTINCT email FROM t2 LEFT JOIN t1 ON kid = t2.id WHERE t1.id IS NULL LIMIT 10;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 system PRIMARY,kid NULL NULL NULL 0 const row not found
-1 SIMPLE t2 index NULL e_n 104 NULL 200
+1 SIMPLE t2 index NULL e_n 104 NULL 10
SELECT SQL_CALC_FOUND_ROWS DISTINCT email FROM t2 LEFT JOIN t1 ON kid = t2.id WHERE t1.id IS NULL LIMIT 10;
email
email1
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 40b9e489577..a25183a0e6d 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -3419,7 +3419,7 @@ EXPLAIN
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 9 Using where
-2 DEPENDENT SUBQUERY t1 index NULL a 8 NULL 9 Using filesort
+2 DEPENDENT SUBQUERY t1 index NULL a 8 NULL 1 Using filesort
DROP TABLE t1;
create table t1( f1 int,f2 int);
insert into t1 values (1,1),(2,2);
diff --git a/mysql-test/t/distinct.test b/mysql-test/t/distinct.test
index 7310f98cd16..bfdb5f8b9f8 100644
--- a/mysql-test/t/distinct.test
+++ b/mysql-test/t/distinct.test
@@ -97,7 +97,7 @@ explain SELECT t1.a from t1 group by a order by a desc;
explain SELECT distinct t1.a from t1 order by a desc limit 1;
explain SELECT distinct a from t3 order by a desc limit 2;
explain SELECT distinct a,b from t3 order by a+1;
-explain SELECT distinct a,b from t3 order by a limit 10;
+explain SELECT distinct a,b from t3 order by a limit 2;
explain SELECT a,b from t3 group by a,b order by a+1;
drop table t1,t2,t3,t4;
diff --git a/mysql-test/t/order_by.test b/mysql-test/t/order_by.test
index 1e520da9f00..fb08dbe74e6 100644
--- a/mysql-test/t/order_by.test
+++ b/mysql-test/t/order_by.test
@@ -739,3 +739,40 @@ INSERT INTO t2 VALUES (1,1),(1,2),(2,1),(2,2);
EXPLAIN SELECT 1 FROM t1,t2 WHERE t1.b=2 AND t1.a=t2.a ORDER BY t2.b;
DROP TABLE t1,t2;
+
+# End of 5.0
+
+#
+# Bug #28404: query with ORDER BY and ref access
+#
+
+CREATE TABLE t1(
+ id int auto_increment PRIMARY KEY, c2 int, c3 int, INDEX k2(c2), INDEX k3(c3));
+
+INSERT INTO t1 (c2,c3) VALUES
+ (31,34),(35,38),(34,31),(32,35),(31,39),
+ (11,14),(15,18),(14,11),(12,15),(11,19);
+
+INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1;
+INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1;
+INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1;
+INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1;
+INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1;
+INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1;
+INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1;
+INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1;
+INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1;
+INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1;
+INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1;
+INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1;
+
+SELECT COUNT(*) FROM t1;
+
+EXPLAIN SELECT id,c3 FROM t1 WHERE c2=11 ORDER BY c3 LIMIT 20;
+EXPLAIN SELECT id,c3 FROM t1 WHERE c2=11 ORDER BY c3 LIMIT 100;
+EXPLAIN SELECT id,c3 FROM t1 WHERE c2 BETWEEN 10 AND 12 ORDER BY c3 LIMIT 100;
+EXPLAIN SELECT id,c3 FROM t1 WHERE c2 BETWEEN 10 AND 12 ORDER BY c3 LIMIT 2000;
+
+SELECT id,c3 FROM t1 WHERE c2=11 ORDER BY c3 LIMIT 20;
+
+DROP TABLE t1;
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 6a381c1f367..c079f2e030f 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -6450,6 +6450,7 @@ void JOIN_TAB::cleanup()
quick= 0;
x_free(cache.buff);
cache.buff= 0;
+ limit= 0;
if (table)
{
if (table->key_read)
@@ -12588,9 +12589,12 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit,
{
int ref_key;
uint ref_key_parts;
+ int order_direction;
+ uint used_key_parts;
TABLE *table=tab->table;
SQL_SELECT *select=tab->select;
key_map usable_keys;
+ QUICK_SELECT_I *save_quick;
DBUG_ENTER("test_if_skip_sort_order");
LINT_INIT(ref_key_parts);
@@ -12625,6 +12629,7 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit,
else if (select && select->quick) // Range found by opt_range
{
int quick_type= select->quick->get_type();
+ save_quick= select->quick;
/*
assume results are not ordered when index merge is used
TODO: sergeyp: Results of all index merge selects actually are ordered
@@ -12644,8 +12649,6 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit,
/*
We come here when there is a REF key.
*/
- int order_direction;
- uint used_key_parts;
if (!usable_keys.is_set(ref_key))
{
/*
@@ -12706,63 +12709,30 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit,
}
/* Check if we get the rows in requested sorted order by using the key */
if (usable_keys.is_set(ref_key) &&
- (order_direction = test_if_order_by_key(order,table,ref_key,
- &used_key_parts)))
- {
- if (order_direction == -1) // If ORDER BY ... DESC
- {
- if (select && select->quick)
- {
- /*
- Don't reverse the sort order, if it's already done.
- (In some cases test_if_order_by_key() can be called multiple times
- */
- if (!select->quick->reverse_sorted())
- {
- QUICK_SELECT_DESC *tmp;
- int quick_type= select->quick->get_type();
- if (quick_type == QUICK_SELECT_I::QS_TYPE_INDEX_MERGE ||
- quick_type == QUICK_SELECT_I::QS_TYPE_ROR_INTERSECT ||
- quick_type == QUICK_SELECT_I::QS_TYPE_ROR_UNION ||
- quick_type == QUICK_SELECT_I::QS_TYPE_GROUP_MIN_MAX)
- DBUG_RETURN(0); // Use filesort
-
- /* ORDER BY range_key DESC */
- tmp= new QUICK_SELECT_DESC((QUICK_RANGE_SELECT*)(select->quick),
- used_key_parts);
- if (!tmp || tmp->error)
- {
- delete tmp;
- DBUG_RETURN(0); // Reverse sort not supported
- }
- select->quick=tmp;
- }
- DBUG_RETURN(1);
- }
- if (tab->ref.key_parts < used_key_parts)
- {
- /*
- SELECT * FROM t1 WHERE a=1 ORDER BY a DESC,b DESC
-
- Use a traversal function that starts by reading the last row
- with key part (A) and then traverse the index backwards.
- */
- tab->read_first_record= join_read_last_key;
- tab->read_record.read_record= join_read_prev_same;
- /* fall through */
- }
- }
- else if (select && select->quick)
- select->quick->sorted= 1;
- DBUG_RETURN(1); /* No need to sort */
- }
+ (order_direction= test_if_order_by_key(order,table,ref_key,
+ &used_key_parts)))
+ goto check_reverse_order;
}
- else
{
- /* check if we can use a key to resolve the group */
- /* Tables using JT_NEXT are handled here */
+ /*
+ Check whether there is an index compatible with the given order
+ usage of which is cheaper than usage of the ref_key index (ref_key>=0)
+ or a table scan.
+ It may be the case if ORDER/GROUP BY is used with LIMIT.
+ */
uint nr;
key_map keys;
+ uint best_key_parts;
+ int best_key_direction;
+ ha_rows best_records;
+ double read_time;
+ int best_key= -1;
+ bool is_best_covering= FALSE;
+ double fanout= 1;
+ JOIN *join= tab->join;
+ uint tablenr= tab - join->join_tab;
+ ha_rows table_records= table->file->stats.records;
+ bool group= join->group && order == join->group_list;
/*
filesort() and join cache are usually faster than reading in
@@ -12775,7 +12745,7 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit,
resolved with a key; This is because filesort() is usually faster than
retrieving all rows through an index.
*/
- if (select_limit >= table->file->stats.records)
+ if (select_limit >= table_records)
{
keys= *table->file->keys_to_use_for_scanning();
keys.merge(table->covering_keys);
@@ -12786,38 +12756,224 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit,
This is to allow users to use index in ORDER BY.
*/
if (table->force_index)
- keys.merge(table->keys_in_use_for_query);
+ keys.merge(group ? table->keys_in_use_for_group_by :
+ table->keys_in_use_for_order_by);
keys.intersect(usable_keys);
}
else
keys= usable_keys;
+ read_time= join->best_positions[tablenr].read_time;
+ for (uint i= tablenr+1; i < join->tables; i++)
+ fanout*= join->best_positions[i].records_read; // fanout is always >= 1
+
for (nr=0; nr < table->s->keys ; nr++)
{
- uint not_used;
- if (keys.is_set(nr))
+ int direction;
+ if (keys.is_set(nr) &&
+ (direction= test_if_order_by_key(order, table, nr, &used_key_parts)))
{
- int flag;
- if ((flag= test_if_order_by_key(order, table, nr, &not_used)))
+ bool is_covering= table->covering_keys.is_set(nr) ||
+ nr == table->s->primary_key &&
+ table->file->primary_key_is_clustered();
+
+ /*
+ Don't use an index scan with ORDER BY without limit.
+ For GROUP BY without limit always use index scan
+ if there is a suitable index.
+ Why we hold to this asymmetry hardly can be explained
+ rationally. It's easy to demonstrate that using
+ temporary table + filesort could be cheaper for grouping
+ queries too.
+ */
+ if (is_covering ||
+ select_limit != HA_POS_ERROR ||
+ ref_key < 0 && (group || table->force_index))
+ {
+ double rec_per_key;
+ double index_scan_time;
+ KEY *keyinfo= tab->table->key_info+nr;
+ if (select_limit == HA_POS_ERROR)
+ select_limit= table_records;
+ if (group)
+ {
+ rec_per_key= keyinfo->rec_per_key[used_key_parts-1];
+ set_if_bigger(rec_per_key, 1);
+ /*
+ With a grouping query each group containing on average
+ rec_per_key records produces only one row that will
+ be included into the result set.
+ */
+ if (select_limit > table_records/rec_per_key)
+ select_limit= table_records;
+ else
+ select_limit= (ha_rows) (select_limit*rec_per_key);
+ }
+ /*
+ If tab=tk is not the last joined table tn then to get first
+ L records from the result set we can expect to retrieve
+ only L/fanout(tk,tn) where fanout(tk,tn) says how many
+ rows in the record set on average will match each row tk.
+ Usually our estimates for fanouts are too pessimistic.
+ So the estimate for L/fanout(tk,tn) will be too optimistic
+ and as result we'll choose an index scan when using ref/range
+ access + filesort will be cheaper.
+ */
+ select_limit= (ha_rows) (select_limit < fanout ?
+ 1 : select_limit/fanout);
+ /*
+ We assume that each of the tested indexes is not correlated
+ with ref_key. Thus, to select first N records we have to scan
+ N/selectivity(ref_key) index entries.
+ selectivity(ref_key) = #scanned_records/#table_records =
+ table->quick_condition_rows/table_records.
+ In any case we can't select more than #table_records.
+ N/(table->quick_condition_rows/table_records) > table_records
+ <=> N > table->quick_condition_rows.
+ */
+ if (select_limit > table->quick_condition_rows)
+ select_limit= table_records;
+ else
+ select_limit= (ha_rows) (select_limit *
+ (double) table_records /
+ table->quick_condition_rows);
+ rec_per_key= keyinfo->rec_per_key[keyinfo->key_parts-1];
+ set_if_bigger(rec_per_key, 1);
+ /*
+ Here we take into account the fact that rows are
+ accessed in sequences rec_per_key records in each.
+ Rows in such a sequence are supposed to be ordered
+ by rowid/primary key. When reading the data
+ in a sequence we'll touch not more pages than the
+ table file contains.
+ TODO. Use the formula for a disk sweep sequential access
+ to calculate the cost of accessing data rows for one
+ index entry.
+ */
+ index_scan_time= select_limit/rec_per_key *
+ min(rec_per_key, table->file->scan_time());
+ if (is_covering ||
+ ref_key < 0 && (group || table->force_index) ||
+ index_scan_time < read_time)
+ {
+ ha_rows quick_records= table_records;
+ if (is_best_covering && !is_covering)
+ continue;
+ if (table->quick_keys.is_set(nr))
+ quick_records= table->quick_rows[nr];
+ if (best_key < 0 ||
+ (select_limit <= min(quick_records,best_records) ?
+ keyinfo->key_parts < best_key_parts :
+ quick_records < best_records))
+ {
+ best_key= nr;
+ best_key_parts= keyinfo->key_parts;
+ best_records= quick_records;
+ is_best_covering= is_covering;
+ best_key_direction= direction;
+ }
+ }
+ }
+ }
+ }
+ if (best_key >= 0)
+ {
+ bool quick_created= FALSE;
+ if (table->quick_keys.is_set(best_key) && best_key != ref_key)
+ {
+ key_map map;
+ map.clear_all(); // Force the creation of quick select
+ map.set_bit(best_key); // only best_key.
+ quick_created=
+ select->test_quick_select(join->thd, map, 0,
+ join->select_options & OPTION_FOUND_ROWS ?
+ HA_POS_ERROR :
+ join->unit->select_limit_cnt,
+ 0) > 0;
+ }
+ if (!no_changes)
+ {
+ if (!quick_created)
{
- if (!no_changes)
- {
- tab->index=nr;
- tab->read_first_record= (flag > 0 ? join_read_first:
- join_read_last);
- tab->type=JT_NEXT; // Read with index_first(), index_next()
- if (table->covering_keys.is_set(nr))
- {
- table->key_read=1;
- table->file->extra(HA_EXTRA_KEYREAD);
- }
- }
- DBUG_RETURN(1);
+ tab->index= best_key;
+ tab->read_first_record= best_key_direction > 0 ?
+ join_read_first:join_read_last;
+ tab->type=JT_NEXT; // Read with index_first(), index_next()
+ if (table->covering_keys.is_set(best_key))
+ {
+ table->key_read=1;
+ table->file->extra(HA_EXTRA_KEYREAD);
+ }
+ table->file->ha_index_or_rnd_end();
+ if (join->select_options & SELECT_DESCRIBE)
+ {
+ tab->ref.key= -1;
+ tab->ref.key_parts= 0;
+ if (tab->select)
+ tab->select->quick= 0;
+ if (select_limit < table_records)
+ tab->limit= select_limit;
+ }
+ }
+ }
+ used_key_parts= best_key_parts;
+ order_direction= best_key_direction;
+ }
+ else
+ DBUG_RETURN(0);
+ }
+
+check_reverse_order:
+ if (order_direction == -1) // If ORDER BY ... DESC
+ {
+ if (select && select->quick)
+ {
+ /*
+ Don't reverse the sort order, if it's already done.
+ (In some cases test_if_order_by_key() can be called multiple times
+ */
+ if (!select->quick->reverse_sorted())
+ {
+ QUICK_SELECT_DESC *tmp;
+ int quick_type= select->quick->get_type();
+ if (quick_type == QUICK_SELECT_I::QS_TYPE_INDEX_MERGE ||
+ quick_type == QUICK_SELECT_I::QS_TYPE_ROR_INTERSECT ||
+ quick_type == QUICK_SELECT_I::QS_TYPE_ROR_UNION ||
+ quick_type == QUICK_SELECT_I::QS_TYPE_GROUP_MIN_MAX)
+ {
+ tab->limit= 0;
+ select->quick= save_quick;
+ DBUG_RETURN(0); // Use filesort
+ }
+
+ /* ORDER BY range_key DESC */
+ tmp= new QUICK_SELECT_DESC((QUICK_RANGE_SELECT*)(select->quick),
+ used_key_parts);
+ if (!tmp || tmp->error)
+ {
+ delete tmp;
+ select->quick= save_quick;
+ tab->limit= 0;
+ DBUG_RETURN(0); // Reverse sort not supported
}
+ select->quick=tmp;
}
}
+ else if (tab->ref.key >= 0 && tab->ref.key_parts < used_key_parts)
+ {
+ /*
+ SELECT * FROM t1 WHERE a=1 ORDER BY a DESC,b DESC
+
+ Use a traversal function that starts by reading the last row
+ with key part (A) and then traverse the index backwards.
+ */
+ tab->read_first_record= join_read_last_key;
+ tab->read_record.read_record= join_read_prev_same;
+ }
}
- DBUG_RETURN(0); // Can't use index.
+ else if (select && select->quick)
+ select->quick->sorted= 1;
+ DBUG_RETURN(1);
}
@@ -15524,7 +15680,7 @@ static void select_describe(JOIN *join, bool need_tmp_table, bool need_order,
if (tab->select && tab->select->quick)
examined_rows= tab->select->quick->records;
else if (tab->type == JT_NEXT || tab->type == JT_ALL)
- examined_rows= tab->table->file->records();
+ examined_rows= tab->limit ? tab->limit : tab->table->file->records();
else
examined_rows=(ha_rows)join->best_positions[i].records_read;
diff --git a/sql/sql_select.h b/sql/sql_select.h
index 98f2a7829dd..61df43094c2 100644
--- a/sql/sql_select.h
+++ b/sql/sql_select.h
@@ -194,6 +194,12 @@ typedef struct st_join_table {
enum join_type type;
bool cached_eq_ref_table,eq_ref_table,not_used_in_distinct;
bool sorted;
+ /*
+ If it's not 0 the number stored this field indicates that the index
+ scan has been chosen to access the table data and we expect to scan
+ this number of rows for the table.
+ */
+ ha_rows limit;
TABLE_REF ref;
JOIN_CACHE cache;
JOIN *join;
View Lorry Controller Status