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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
