Query caching? The second response probably came from the cache. To be absolutely sure (a.k.a "overkill") that you have no cached results, you can restart the server between each query. Or, you can use the RESET QUERY CACHE command to clear your cache without the restart.
http://dev.mysql.com/doc/mysql/en/RESET.html The quoting rule is: If it's a NUMBER, don't quote it. Strings get quotes, so do dates. Database elements (tables, columns, etc. ) whose names are a reserved word or contain invalid characters (like spaces) or are otherwise invalid would need to be surrounded by backticks (`) not single quotes (') to be referenced. The use of invalid names is supported for cross platform compatibility (through the backtick mechanism) but is *strongly discouraged* as it usually indicates a poor design choice. Shawn Green Database Administrator Unimin Corporation - Spruce Pine "Dave Juntgen" <[EMAIL PROTECTED]> wrote on 12/15/2004 10:38:03 AM: > 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 > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] >
