The select query does not use the indexes available

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

The select query does not use the indexes available

Post by raghavan20 »

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...

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 |
+----+-------------+-------+------+---------------+------+---------+------+------+-------
----------------------------------------------+
2. How do drop all the indexes for a table in a single query?
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Post by Jenk »

Code: Select all

DROP INDEX `index_name` ON `table`
Have you tried

Code: Select all

DROP INDEX * ON `table`
Post Reply