List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:December 15 2004 4:09pm
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:
> 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
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