From: Dan Nelson
Date: December 15 2004 4:09pm
Subject: Re: To use Quotes or not to, that's the question.
List-Archive: http://lists.mysql.com/mysql/177358
Message-Id: <20041215160937.GH37432@dan.emsphone.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

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