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]
