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 / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
