subramani subramani - 1 month ago 8
MySQL Question

drop index indexname is not working fine when i try to use this query in mysql browser

I get an error when i try to

drop
my index, or when I try to view my index using this kind of query
SHOW INDEX
or
DROP INDEX <INDEXNAME>
.

The error message is:

mysqlserver version for right syntax to use near at line 1"".

Answer

For seeing indexes

show index from myTable;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| myTable |          0 | PRIMARY  |            1 | id          | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| myTable |          1 | title    |            1 | title       | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               |
| myTable |          1 | price    |            1 | price       | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

For Dropping index

ALTER TABLE TABLE_NAME DROP INDEX INDEX_NAME;

Example

show create table myTable\G
*************************** 1. row ***************************
       Table: myTable
Create Table: CREATE TABLE `myTable` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `price` mediumint(9) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `title` (`title`),
  KEY `price` (`price`),
  KEY `idx_price_title` (`price`,`title`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
1 row in set (0.02 sec)

I need to drop index idx_price_title

ALTER TABLE myTable DROP INDEX idx_price_title;
Query OK, 0 rows affected (0.18 sec)
Records: 0  Duplicates: 0  Warnings: 0

Check whether or not the index exists

show create table myTable\G
*************************** 1. row ***************************
       Table: myTable
Create Table: CREATE TABLE `myTable` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `price` mediumint(9) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `title` (`title`),
  KEY `price` (`price`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
1 row in set (0.00 sec)