In the last episode (Dec 15), Dave Juntgen said:
> > ext_doc_id is a CHAR type, which means that you may have two rows,
> > "0412625", and "412625", both of which match the expression "WHERE
> > ext_doc_id=412625". MySQL can't use an index because it has to
> > convert each field to a number before doing the comparison. Your
> > second query is doing a direct string comparison, so MySQL can use
> > the index. If you know you will only be storing numbers in
> > ext_doc_id, consider converting it to an INT or BIGINT and save
> > yourself some disk space.
>
> This was my assumption as well, int->string conversion each time. But
> if MySQL knows that the index is a char and I send a int, why not
> just do the int->string conversion once and store the value in a
> buffer and "then" call the index?
Because that wouldn't be right. If it did that, then in my example, it
would only match 1 record instead of two. If the two fields were
"0412625" and " 0412625", it wouldn't match either of them. It has to
convert each field to an int to see whether it evaluates to 412625 or
not. I've put some sample SQL at the bottom of my post showing this.
If mysql had computed indexes (it doesn't), you could create an index
on (ext_doc_id+0), and then use WHERE (ext_doc_id+0) = 41265 in your
query. That would use the index.
mysql> CREATE TABLE test ( myvalue char(30) );
mysql> INSERT INTO test VALUES ("412625"),(" 412625"),("0412625");
mysql> SELECT * FROM test WHERE myvalue = 412625;
+---------+
| myvalue |
+---------+
| 412625 |
| 0412625 |
| 412625 |
+---------+
mysql> SELECT * FROM test WHERE myvalue = "412625";
+---------+
| myvalue |
+---------+
| 412625 |
+---------+
--
Dan Nelson
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
