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
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
