diff options
Diffstat (limited to 'mysql-test/main/costs.result')
| -rw-r--r-- | mysql-test/main/costs.result | 42 |
1 files changed, 21 insertions, 21 deletions
diff --git a/mysql-test/main/costs.result b/mysql-test/main/costs.result index 879d586790b..27ce1cbbfb0 100644 --- a/mysql-test/main/costs.result +++ b/mysql-test/main/costs.result @@ -7,11 +7,11 @@ insert into t1 values(20,2,2,2,2),(21,3,4,5,6); explain select sum(e) as "table_scan" from t1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 12 -Last_query_cost 5.500000 +Last_query_cost 0.012556 explain select sum(a) as "index scan" from t1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL PRIMARY 4 NULL 12 Using index -Last_query_cost 3.202929 +Last_query_cost 0.007441 # # Range scans should be used if we don't examine all rows in the table # @@ -21,71 +21,71 @@ id select_type table type possible_keys key key_len ref rows Extra Last_query_cost 0.000000 explain select count(*) from t1 where a > 0; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index PRIMARY PRIMARY 4 NULL 12 Using where; Using index -Last_query_cost 3.202929 +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 12 Using where; Using index +Last_query_cost 0.002795 explain select count(*) from t1 where a > 1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index PRIMARY PRIMARY 4 NULL 12 Using where; Using index -Last_query_cost 3.202929 +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 12 Using where; Using index +Last_query_cost 0.002795 explain select count(*) from t1 where a > 2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 11 Using where; Using index -Last_query_cost 2.997685 +Last_query_cost 0.002665 # # Shorter indexes are prefered over longer indexs # explain select sum(a+b) from t1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL ba 9 NULL 12 Using index -Last_query_cost 3.204394 +Last_query_cost 0.007441 explain select count(*) from t1 where b between 5 and 10; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range ba,bda ba 5 NULL 6 Using where; Using index -Last_query_cost 1.872197 +Last_query_cost 0.002015 explain select sum(b+c) from t1 where b between 5 and 6 and c between 5 and 6; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range ba,bda,cba,cb cb 10 NULL 2 Using where; Using index -Last_query_cost 0.970781 +1 SIMPLE t1 range ba,bda,cba,cb cba 10 NULL 2 Using where; Using index +Last_query_cost 0.001494 # Cost of 'd' should be slightly smaller as key 'ba' is longer than 'd' explain select count(*) from t1 where b > 6; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range ba,bda ba 5 NULL 5 Using where; Using index -Last_query_cost 1.646831 +Last_query_cost 0.001885 explain select count(*) from t1 where d > 6; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range d d 5 NULL 5 Using where; Using index -Last_query_cost 1.646343 +Last_query_cost 0.001885 # # Check covering index usage # explain select a,b,c from t1 where a=b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL cba 14 NULL 12 Using where; Using index -Last_query_cost 3.205859 +Last_query_cost 0.007441 # # Prefer ref keys over ranges # explain select count(*) from t1 where b=2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref ba,bda ba 5 const 2 Using index -Last_query_cost 0.950732 +Last_query_cost 0.001059 explain select count(*) from t1 where b=2 and c=2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref ba,bda,cba,cb cb 10 const,const 2 Using index -Last_query_cost 0.950781 +1 SIMPLE t1 ref ba,bda,cba,cb cba 10 const,const 2 Using index +Last_query_cost 0.001059 explain select count(*) from t1 where b=3 and c between 3 and 4; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range ba,bda,cba,cb cb 10 NULL 2 Using where; Using index -Last_query_cost 0.970781 +1 SIMPLE t1 range ba,bda,cba,cb cba 10 NULL 2 Using where; Using index +Last_query_cost 0.001494 # # Prefer eq keys over ref keys # explain select a,b,e from t1 where a=10 or a=11; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using index condition -Last_query_cost 2.520488 +Last_query_cost 0.003090 explain select a,b,e from t1 where d=10 or d=11; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range d d 5 NULL 2 Using index condition -Last_query_cost 2.520537 +Last_query_cost 0.003090 drop table t1; |