List:General Discussion« Previous MessageNext Message »
From:SGreen Date:December 15 2004 4:03pm
Subject:Re: To use Quotes or not to, that's the question.
View as plain text  
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" <djuntgen@stripped> 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/mysql?unsub=1
> 

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