List:General Discussion« Previous MessageNext Message »
From:Gleb Paharenko Date:October 19 2005 10:46pm
Subject:Re: implicit cast forces table scan?
View as plain text  
Hello.

I suggest you to check you query with MySQL 4.1.14 which might has a bit
clever optimizer.


Olaf Faaland wrote:
> 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.
> 
> 


-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___     ___ ____  __
  /  |/  /_ __/ __/ __ \/ /    Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   Gleb.Paharenko@stripped
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
       <___/   www.mysql.com



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