not using the index

The following information is so that you can see the version of software
that I am using:

sqltest3# /usr/local/mysql/bin/mysqladmin version -p
Enter password:
/usr/local/mysql/bin/mysqladmin Ver 8.40 Distrib 4.0.12, for
unknown-freebsd4.7
on i386
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Server version 4.0.12-max
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /tmp/mysql.sock
Uptime: 2 days 17 hours 35 min 34 sec

Threads: 2 Questions: 41 Slow queries: 4 Opens: 10 Flush tables: 2 Open
tab
les: 3 Queries per second avg: 0.000

And here is the information for the problem I am having:

mysql> explain select fname,lname,city from ca_ama where lname ='smith'
limit 10;
+--------+------+---------------+------+---------+------+--- -------+--------
-----+
| table | type | possible_keys | key | key_len | ref | rows | Extra
|
+--------+------+---------------+------+---------+------+--- -------+--------
-----+
| ca_ama | ALL | LNAME | NULL | NULL | NULL | 10110471 | Using
where |
+--------+------+---------------+------+---------+------+--- -------+--------
-----+
1 row in set (0.00 sec)

Why would this query NOT use the lname key to determine the results instead
of scanning all the records in the table?



--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: not using the index

Hi,

> mysql> explain select fname,lname,city from ca_ama where lname =3D'smith'
> limit 10;
> +--------+------+---------------+------+---------+------+--- -------+-----=
---
> -----+
> | table | type | possible_keys | key | key_len | ref | rows | Extr=
a
> |
> +--------+------+---------------+------+---------+------+--- -------+-----=
---
> -----+
> | ca_ama | ALL | LNAME | NULL | NULL | NULL | 10110471 | Usin=
g
> where |
> +--------+------+---------------+------+---------+------+--- -------+-----=
---
> -----+
> 1 row in set (0.00 sec)
>=20
> Why would this query NOT use the lname key to determine the results inste=
ad
> of scanning all the records in the table?

I'm not exactly sure, why this is happening. Just in case, what does the
following query return you?

SELECT count(*) FROM ca_ama WHERE lname=3D'smith';


Output of the following commands might also be helpful:

SHOW CREATE TABLE ca_ama;
SHOW TABLE STATUS LIKE 'ca_ama';


Rgds,
Indrek

--=20
MySQL Users Conference and Expo: http://www.mysql.com/events/uc2003/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Indrek Siitan
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, The Support Guy
/_/ /_/\_, /___/\___\_\___/ Uuem=F5isa, Haapsalu, Estonia
<___/ www.mysql.com


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=3Dgcdmb-bugs@m.gmane.org