List:General Discussion« Previous MessageNext Message »
From:Benjamin KRIEF Date:September 1 2003 12:40am
Subject:spaces in index name
View as plain text  
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