List:General Discussion« Previous MessageNext Message »
From:Matt W Date:September 1 2003 1:02am
Subject:Re: spaces in index name
View as plain text  
Hi Ben,

When using column/index names with odd characters (such as spaces), you
need to use backticks (SHIFT + the ~ key) around the name: `index id`

This ALTER TABLE query should make all the changes you want:

ALTER TABLE v2easy0_users
DROP INDEX `login connexion`,
DROP INDEX `index id`,
-- Drop the PRIMARY KEY with 2 id columns
DROP PRIMARY KEY,
-- And re-add it with just one column
ADD PRIMARY KEY (id);

Hope that helps.

Matt


----- Original Message -----
From: "Benjamin KRIEF"
Sent: Sunday, August 31, 2003 7:40 PM
Subject: spaces in index name


> hi all ,
>
> i'm trying to improve performance of a quite big and heavily used
mysql
> set of tables.
> i want to create some indexes on this table , but before this , i'd
like
> to remove the ones created by my predecessor , which are sometimes
> duplicate , and somtimes useless.
>
> here is an output from show index :
>
> -+---------------+------------+-------------------+--------------+----
---------+
> C| Table         | Non_unique | Key_name          | Seq_in_index |
> Column_name |
> -+---------------+------------+-------------------+--------------+----
---------+
> A| v2easy0_users |          0 | PRIMARY           |            1 |
> id          |
> A| v2easy0_users |          0 | PRIMARY           |            2 |
> id          |
> A| v2easy0_users |          0 | login             |            1 |
> login       |
> A| v2easy0_users |          0 | id                |            1 |
> date        |
> A| v2easy0_users |          0 | id                |            2 |
> id          |
> A| v2easy0_users |          0 | id                |            3 |
> login       |
> A| v2easy0_users |          1 | login connexion   |            1 |
> login       |
> A| v2easy0_users |          1 | index id          |            1 |
> id          |
>
> first of all , i'm asking myself : why is there 2 primary keys on the
> same column ?
> i'd want to remove the second index , but since it has the same name
> than the first  , how would i do something like that?
>
> then , i'd like to remove the index "login connexion" , and the index
> "index id" , but the problem is :
>
> mysql> drop index login connexion on v2easy0_users;
> ERROR 1064: You have an error in your SQL syntax near 'connexion on
> v2easy0_users' at line 1
>
> (the same goes with ' and " around the index name)
>
> ! of course , mysql doesn't accept spaces in index names , so why did
he
> accepted it on creation ?
> it seems that i can't remove those index , which are never used by the
> optimizer (i used explain) , and which slow down my insert and
updates...
>
> should i dump then recreate this table?
> i'm a bit stuck here , thanks for your help , and sorry for my english
...
>
> ben

Thread
spaces in index nameBenjamin KRIEF1 Sep
  • Re: spaces in index nameRoger Baklund1 Sep
  • Re: spaces in index nameDan Nelson1 Sep
  • Re: spaces in index nameMatt W1 Sep
    • Re: spaces in index nameBenjamin KRIEF1 Sep
  • Re: spaces in index nameMatt W1 Sep