The select query does not use the indexes available
Posted: Sat Jan 14, 2006 8:08 pm
1. I have two indexes on forums_tbl. Jus' for experimental purposes, I have two indexes one unique and non-unique on the same field title. You can see that the select query does not use any of the indexes available...
2. How do drop all the indexes for a table in a single query?
Code: Select all
mysql> create unique index
-> index_unique_title
-> on
-> forums_tbl
-> (title);
Query OK, 7 rows affected (0.14 sec)
Records: 7 Duplicates: 0 Warnings: 0
mysql> show index from forums_tbl;
+------------+------------+--------------------+--------------+-------------+-----------+
-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation |
Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------+------------+--------------------+--------------+-------------+-----------+
-------------+----------+--------+------+------------+---------+
| forums_tbl | 0 | PRIMARY | 1 | Id | A |
7 | NULL | NULL | | BTREE | |
| forums_tbl | 0 | index_unique_title | 1 | Title | A |
7 | NULL | NULL | | BTREE | |
| forums_tbl | 1 | index_title | 1 | Title | A |
NULL | NULL | NULL | | BTREE | |
+------------+------------+--------------------+--------------+-------------+-----------+
-------------+----------+--------+------+------------+---------+
3 rows in set (1.67 sec)
mysql> explain
-> select * from forums_tbl
-> where title = '%sw%';
+----+-------------+-------+------+---------------+------+---------+------+------+-------
----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
|
+----+-------------+-------+------+---------------+------+---------+------+------+-------
----------------------------------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Imposs
ible WHERE noticed after reading const tables |
+----+-------------+-------+------+---------------+------+---------+------+------+-------
----------------------------------------------+