> 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: 260.459.6270 Fax : 260.459.6271 > -----Original Message----- > From: Dan Nelson [mailto:[EMAIL PROTECTED] > Sent: Wednesday, December 15, 2004 11:10 AM > To: Dave Juntgen > Cc: [EMAIL PROTECTED] > 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 > [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
