RE: To use Quotes or not to, that's the question.

[Dave Juntgen]

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

Thanks,

--Dave

David W. Juntgen
Medical Informatics Engineering Inc.
Phone: [phone]
Fax  : [phone]

> -----Original Message-----
> From: Dan Nelson [mailto:dnelson@stripped]
> Sent: Wednesday, December 15, 2004 11:10 AM
> To: Dave Juntgen
> Cc: mysql@stripped
> Subject: Re: To use Quotes or not to, that's the question.
> 
> In the last episode (Dec 15), Dave Juntgen said:
> > Could someone please explain to me why the second query below is
> > faster then the first query?  The only difference between the two is
> > that ext_doc_id's value is quoted.  Index and column information
> > follow and the table being queried contains approximately 3.5
million
> > rows.
> 
> 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.
> 
> > EXPLAIN SELECT doc_id FROM documents WHERE interface='tasklist' AND
> ext_doc_id=412625;
> >
+-----------+------+---------------+------+---------+-------+-------+---
----
> ------+
> > | table     | type | possible_keys | key  | key_len | ref   | rows
| Extra
> |
> >
+-----------+------+---------------+------+---------+-------+-------+---
----
> ------+
> > | documents | ref  | idx7          | idx7 |      30 | const | 94761
| Using
> where |
> >
+-----------+------+---------------+------+---------+-------+-------+---
----
> ------+
> > 1 row in set (0.03 sec)
> >
> > EXPLAIN SELECT doc_id FROM documents WHERE interface='tasklist' AND
> ext_doc_id='412625';
> >
+-----------+------+---------------+------+---------+-------------+-----
-+--
> -----------+
> > | table     | type | possible_keys | key  | key_len | ref         |
rows |
> Extra       |
> >
+-----------+------+---------------+------+---------+-------------+-----
-+--
> -----------+
> > | documents | ref  | idx7          | idx7 |      60 | const,const |
3 |
> Using where |
> >
+-----------+------+---------------+------+---------+-------------+-----
-+--
> -----------+
> > 1 row in set (0.00 sec)
> >
> >
+---------------------+---------------------+------+-----+--------------
----
> ---+----------------+
> > | Field               | Type                | Null | Key | Default
> | Extra          |
> >
+---------------------+---------------------+------+-----+--------------
----
> ---+----------------+
> > | ext_doc_id          | char(30)            |      |     |
> |                |
> >
+---------------------+---------------------+------+-----+--------------
----
> ---+----------------+
> > 20 rows in set (0.00 sec)
> 
> --
> 	Dan Nelson
> 	dnelson@stripped