List:General Discussion« Previous MessageNext Message »
From:Ying Lu Date:June 8 2005 4:21pm
Subject:'Single quotation mark' makes different results for 'explain select'
View as plain text  
Greetings,

I did the following two explain select ... According to whether I put 
the single quotation mark or not, I will get totally different results:

1. Without single quotation mark:

explain SELECT * FROM test T1 force index (idx_test)
WHERE  *T1.STUDID = 0999999 AND T1.Prog_link = 6666*;

+-------+--------+------------------------+---------+---------+---------------------------------+-------+-------------+
| table | type   | possible_keys          | key     | key_len | 
ref                             | rows  | Extra       |
+-------+--------+------------------------+---------+---------+---------------------------------+-------+-------------+
| T1    | ALL    | idx_test               | NULL    |    NULL | 
NULL                            | 91230 | Using where |
+-------+--------+------------------------+---------+---------+---------------------------------+-------+-------------+

2. With single quotation mark:
explain SELECT * FROM test T1 force index (idx_test)
WHERE  *T1.STUDID = '0999999**' AND T1.Prog_link = '6666'*;

+-------+--------+------------------------+----------+---------+---------------------------------+------+-------------+
| table | type   | possible_keys          | key      | key_len | 
ref                             | rows | Extra       |
+-------+--------+------------------------+----------+---------+---------------------------------+------+-------------+
| T1    | ref    | idx_test               | idx_test |       7 | 
const                           |   27 | Using where |
+-------+--------+------------------------+----------+---------+---------------------------------+------+-------------+


Note:  . create index idx_test on test(studid, prog_link);
           . in test table, studid is varchar(7), prog_link is varchar(4).


Thanks a lot!
Emi



Thread
'Single quotation mark' makes different results for 'explain select'Ying Lu8 Jun