List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:June 8 2005 4:46pm
Subject:Re: 'Single quotation mark' makes different results for 'explain select'
View as plain text  
In the last episode (Jun 08), Ying Lu said:
> By the way, I am using 4.0.18-log on i686.linux2.6.10.
> >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
> |
>
> >+-------+--------+---------------+---------+---------+------+-------+-------------+

This is a numeric comparison, so it has to convert STUDID to a number
to compare.  If you have 3 rows with the following STUDIDs, they will
all match: "0999999", "999999", " 999999".  It can't do index lookups
on all possible string values that convert to 999999, so it does a full
table scan.

> >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 |
>
> >+-------+--------+---------------+----------+---------+-------+------+-------------+

Here you're doing a string comparison, so only the exact value
"0999999" can match.  It can use the index to look up the matching rows
directly.

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

Solution:  either convert your columns to INTEGER, or use string
comparisons and ensure that the fields have a consistent format.

-- 
	Dan Nelson
	dnelson@stripped
Thread
Re: 'Single quotation mark' makes different results for 'explainselect'Ying Lu8 Jun
  • Re: 'Single quotation mark' makes different results for 'explain select'Dan Nelson8 Jun