i hav a table which structure like tis:
+----------+--------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------------+------+-----+---------+----------------+
| no | bigint(20) | | PRI | NULL | auto_increment |
| date | datetime | YES | | NULL | |
| amend_id | varchar(20) binary | | | | |
| type | varchar(8) binary | | MUL | n | |
| doc_no | varchar(20) binary | YES | | NULL | |
| activity | varchar(20) | YES | MUL | NULL | |
+----------+--------------------+------+-----+---------+----------------+
and when i type this query with the 'activity LIKE 'update(old)', it'll use the index,
explain SELECT * FROM amendment WHERE type='DO' AND activity LIKE 'update(old)';
+-----------+-------+------------------+------+---------+------+------+------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-----------+-------+------------------+------+---------+------+------+------------+
| amendment | range | type,activity,ac | ac | 21 | NULL | 40 | where used |
+-----------+-------+------------------+------+---------+------+------+------------+
but when i type 'activity LIKE 'update%' , it didn't use index. It get throught the whole table record.
explain SELECT * FROM amendment WHERE type='DO' AND activity LIKE 'update%';
+-----------+------+------------------+------+---------+------+------+------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-----------+------+------------------+------+---------+------+------+------------+
| amendment | ALL | type,activity,ac | NULL | NULL | NULL | 296 | where used |
+-----------+------+------------------+------+---------+------+------+------------+
the manual of mysql say index can perform like tis, so wat wrong with my query or my table structure?
