List:General Discussion« Previous MessageNext Message »
From:Dave Juntgen Date:December 15 2004 3:38pm
Subject:To use Quotes or not to, that's the question.
View as plain text  
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

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