Re: spaces in index name

[Benjamin KRIEF]

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
   



 



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]