List:General Discussion« Previous MessageNext Message »
From:Olaf Faaland Date:October 19 2005 6:15pm
Subject:implicit cast forces table scan?
View as plain text  
Hi,

I am currently using mysql 4.0.18 as distributed with red hat Linux.  I find
when I perform a select on a varchar(30) field, the index is used only if I
have quoted the value in the where clause.  Otherwise, mysql performs a
table scan.

The queries in question are:

This query uses the index:
mysql> explain
    -> select itran_log_date, itran_log_actionid from itran_log where
    -> itran_log_actionid = "170807";

This query performs a table scan:
mysql> explain
    -> select itran_log_date, itran_log_actionid from itran_log where
    -> itran_log_actionid = 170807;

My question is this: is the issue here that mysql is converting every single
itran_log_actionid value, from all 1.5 million rows, and hence the index is
not useful and not used?  My initial assumption was that the constant value
170807 in the second query, would be converted to text before the query was
executed, and so the index could be used.  This does not seem to be the
case.

I ask both for my own edification, and also because it seems to me this
should be mentioned in the manual for newbies like myself.

thanks,
Olaf

Details on versions, table structures, indexes, etc. below
==========================================


======
$ rpm -qa | grep -i mysql
MySQL-shared-compat-4.0.15-0
MySQL-client-4.0.18-0
php-mysql-4.1.2-7.2.6
MySQL-server-4.0.18-0

$ /usr/bin/mysql -V
/usr/bin/mysql  Ver 12.22 Distrib 4.0.18, for pc-linux (i686)
======

mysql> describe itran_log;
+------------------------+--------------+------+-----+------------+---------
-------+
| Field                  | Type         | Null | Key | Default    | Extra
|
+------------------------+--------------+------+-----+------------+---------
-------+
| itran_user_id          | varchar(100) |      |     |            |
|
| itran_log_date         | date         |      | MUL | 0000-00-00 |
|
| itran_log_time         | time         |      |     | 00:00:00   |
|
| itran_log_filename     | varchar(100) |      |     |            |
|
| itran_log_action       | varchar(25)  |      | MUL |            |
|
| itran_log_actionid     | varchar(30)  |      | MUL |            |
|
| itran_site_id          | varchar(100) | YES  | MUL | NULL       |
|
| itran_log_instructions | text         |      |     |            |
|
| itran_log_id           | bigint(20)   |      | PRI | NULL       |
auto_increment |
+------------------------+--------------+------+-----+------------+---------
-------+

mysql> show indexes from itran_log;
+-----------+------------+-----------------------+--------------+-----------
---------+-----------+-------------+----------+--------+------+------------+
---------+
| Table     | Non_unique | Key_name              | Seq_in_index |
Column_name        | Collation | Cardinality | Sub_part | Packed | Null |
Index_type | Comment |
+-----------+------------+-----------------------+--------------+-----------
---------+-----------+-------------+----------+--------+------+------------+
---------+
| itran_log |          0 | PRIMARY               |            1 |
itran_log_id       | A         |     1500793 |     NULL | NULL   |      |
BTREE      |         |
| itran_log |          1 | itran_site_id_ix      |            1 |
itran_site_id      | A         |        NULL |       15 | NULL   | YES  |
BTREE      |         |
| itran_log |          1 | itran_log_action_ix   |            1 |
itran_log_action   | A         |        NULL |        3 | NULL   |      |
BTREE      |         |
| itran_log |          1 | itran_log_actionid_ix |            1 |
itran_log_actionid | A         |        NULL |     NULL | NULL   |      |
BTREE      |         |
| itran_log |          1 | itran_log_date_ix     |            1 |
itran_log_date     | A         |        NULL |     NULL | NULL   |      |
BTREE      |         |
+-----------+------------+-----------------------+--------------+-----------
---------+-----------+-------------+----------+--------+------+------------+
---------+

mysql> explain
    -> select itran_log_date, itran_log_actionid from itran_log where
    -> itran_log_actionid = 170807;
+-----------+------+-----------------------+------+---------+------+--------
-+-------------+
| table     | type | possible_keys         | key  | key_len | ref  | rows
| Extra       |
+-----------+------+-----------------------+------+---------+------+--------
-+-------------+
| itran_log | ALL  | itran_log_actionid_ix | NULL |    NULL | NULL | 1500775
| Using where |
+-----------+------+-----------------------+------+---------+------+--------
-+-------------+
1 row in set (0.02 sec)

mysql> explain
    -> select itran_log_date, itran_log_actionid from itran_log where
    -> itran_log_actionid = "170807";
+-----------+------+-----------------------+-----------------------+--------
-+-------+------+-------------+
| table     | type | possible_keys         | key                   | key_len
| ref   | rows | Extra       |
+-----------+------+-----------------------+-----------------------+--------
-+-------+------+-------------+
| itran_log | ref  | itran_log_actionid_ix | itran_log_actionid_ix |      30
| const |    4 | Using where |
+-----------+------+-----------------------+-----------------------+--------
-+-------+------+-------------+
1 row in set (0.00 sec)

Olaf Faaland
Sovran Inc.

Thread
implicit cast forces table scan?Olaf Faaland19 Oct
  • Re: implicit cast forces table scan?Jeff Smelser19 Oct
  • Re: implicit cast forces table scan?Michael Stassen21 Oct
  • Re: implicit cast forces table scan?Gleb Paharenko21 Oct
    • Re: implicit cast forces table scan?Michael Stassen21 Oct
      • Re: implicit cast forces table scan?Gleb Paharenko23 Oct
        • Re: implicit cast forces table scan?Michael Stassen24 Oct
RE: implicit cast forces table scan?Olaf Faaland21 Oct