List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:December 15 2004 6:02pm
Subject:Re: To use Quotes or not to, that's the question.
View as plain text  
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
	dnelson@stripped
Thread
To use Quotes or not to, that's the question.Dave Juntgen15 Dec
  • Re: To use Quotes or not to, that's the question.Duncan Hill15 Dec
  • Re: To use Quotes or not to, that's the question.SGreen15 Dec
  • Re: To use Quotes or not to, that's the question.Dan Nelson15 Dec
RE: To use Quotes or not to, that's the question.Dave Juntgen15 Dec
  • Re: To use Quotes or not to, that's the question.Dan Nelson15 Dec