I created a table of name/address/phone number with about 13 million records.
I created an index for each field.
mysql> show index from list1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+----
-----+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Com
ment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+----
-----+
| list1 | 1 | phone | 1 | phone | NULL | 1 | NULL | NULL | YES | FULLTEXT |
|
| list1 | 1 | lname | 1 | lname | NULL | 1 | NULL | NULL | YES | FULLTEXT |
|
| list1 | 1 | fname | 1 | fname | NULL | 1 | NULL | NULL | YES | FULLTEXT |
|
| list1 | 1 | street | 1 | street | NULL | 1 | NULL | NULL | YES | FULLTEXT |
|
| list1 | 1 | city | 1 | city | NULL | 1 | NULL | NULL | YES | FULLTEXT |
|
| list1 | 1 | postcode | 1 | postcode | NULL | 1 | NULL | NULL | YES | FULLTEXT |
|
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+----
-----+
6 rows in set (0.00 sec)




mysql> explain select * from list1 where lname="TEARLE";
+----+-------------+-------+------+---------------+------+---------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+----------+-------------+
| 1 | SIMPLE | list1 | ALL | lname | NULL | NULL | NULL | 13063030 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+----------+-------------+
1 row in set (0.00 sec)

mysql>
So why does a simple query like
Code:
use clist1;  
explain select * from list1 where lname="TEARLE"                            
select * from list1 where lname="TEARLE";
take 15 seconds, and not use the index?