List:General Discussion« Previous MessageNext Message »
From:Benjamin KRIEF Date:September 1 2003 1:17am
Subject:Re: spaces in index name
View as plain text  
thanks for all your answers ! i'd never imagine that mailing-list to be 
so active and efficient!

matt : does mysql locks the table during the whole alter table execution?


Matt W wrote:

>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