Hi!
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.
Thanks!
--Dave
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
|
+---------------------+---------------------+------+-----+---------------------+----------------+
| doc_id | int(10) unsigned | | PRI | NULL |
auto_increment |
| revision_number | smallint(6) | | | 0 |
|
| user_id | int(10) unsigned | | MUL | 0 |
|
| origin_id | int(10) unsigned | | MUL | 0 |
|
| pat_id | int(10) unsigned | | MUL | 0 |
|
| doc_type | char(10) | | MUL | |
|
| storage_type | int(10) unsigned | | | 0 |
|
| storage_id | bigint(20) unsigned | | | 0 |
|
| volume_id | char(1) | YES | | NULL |
|
| filename | char(255) | YES | | NULL |
|
| service_location | char(10) | YES | | NULL |
|
| origin_date | datetime | | | 0000-00-00 00:00:00 |
|
| enter_date | datetime | | MUL | 0000-00-00 00:00:00 |
|
| revision_date | datetime | | | 0000-00-00 00:00:00 |
|
| service_date | datetime | | | 0000-00-00 00:00:00 |
|
| approx_service_date | tinyint(1) unsigned | | | 0 |
|
| review_date | datetime | | | 0000-00-00 00:00:00 |
|
| review_user_id | int(11) | | | 0 |
|
| ext_doc_id | char(30) | | | |
|
| interface | char(30) | | MUL | |
|
+---------------------+---------------------+------+-----+---------------------+----------------+
20 rows in set (0.00 sec)
mysql> show index from documents;
+-----------+------------+----------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation |
Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------+------------+----------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
| documents | 0 | PRIMARY | 1 | doc_id | A |
3566754 | NULL | NULL | | BTREE | |
| documents | 1 | idx2 | 1 | pat_id | A |
89168 | NULL | NULL | | BTREE | |
| documents | 1 | idx2 | 2 | service_date | A |
1188918 | NULL | NULL | | BTREE | |
| documents | 1 | idx3 | 1 | pat_id | A |
89168 | NULL | NULL | | BTREE | |
| documents | 1 | idx3 | 2 | doc_type | A |
891688 | NULL | NULL | | BTREE | |
| documents | 1 | idx3 | 3 | service_date | A |
1783377 | NULL | NULL | | BTREE | |
| documents | 1 | idx4 | 1 | user_id | A |
223 | NULL | NULL | | BTREE | |
| documents | 1 | idx4 | 2 | revision_date | A |
1783377 | NULL | NULL | | BTREE | |
| documents | 1 | idx5 | 1 | origin_id | A |
289 | NULL | NULL | | BTREE | |
| documents | 1 | idx5 | 2 | origin_date | A |
1783377 | NULL | NULL | | BTREE | |
| documents | 1 | idx7 | 1 | interface | A |
10 | NULL | NULL | | BTREE | |
| documents | 1 | idx7 | 2 | ext_doc_id | A |
63692 | NULL | NULL | | BTREE | |
| documents | 1 | idx8 | 1 | doc_type | A |
255 | NULL | NULL | | BTREE | |
| documents | 1 | idx8 | 2 | storage_type | A |
488 | NULL | NULL | | BTREE | |
| documents | 1 | idx9 | 1 | enter_date | A |
1783377 | NULL | NULL | | BTREE | |
+-----------+------------+----------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
David W. Juntgen
Medical Informatics Engineering Inc.
Phone: 260.459.6270
Fax : 260.459.6271
