MySQL rename and indexes

[der.hans]

Am 03. Nov, 2006 schwätzte der.hans so:

moin moin,

> I've got a couple of MySQL questions.
>
> I see the "show index from $table" option for the mysql client, but it
> doesn't list index names. How does one obtain the index names?

OK, if I hadn't gotten interrupted so many times today I'd've gotten to
the tests I was trying to run and relized the Key_name is the index name
and it's tied to the particular table. Most cool, actually.

> When renaming a table do the indexes for the table follow it to the new
> name? If they do, do the index names also get adjusted?

Did some testing and the indexes do follow the table. Since the indexes
are tied to the table there's no need to rename them.

> Any tips on creating a new table and indexes based on another table and
> its indexes?

create_table=`query_wrapper $db "show create table $table" | tail -1 | sed -e 's/.*CREATE TABLE/CREATE TABLE/' -e 's/$table/&_new/g'`
retstat=$?

if [ 0 -ne $retstat ] ; then
 	echo "ERROR: Couldn't get a create table definition for $table." >&2
 	exit $retstat
fi

query_wrapper reports "$create_table"

I also found out that MySQL can put multiple table renames into a single
rename command such that if one rename fails none are done and everything
is left as it was.

mysql "rename table one to two, two to three, three to one" $db

Most cool.

ciao,

der.hans
-- 
#  https://www.LuftHans.com/        http://www.CiscoLearning.org/
#  Sysdamin Days Phoenix, 6-7 Nov, now with edu discount  https://LOPSA.org/
<moose> Roses are #FF0000,
<moose> Violets are #0000FF,
<moose> All my base,
<moose> Are belong to you.