Sometimes we need to quickly rebuild existed table structure. Mysql provided mysqldump options to facilitate this.

mysqldump -uroot -ppassword --no-data databasename > table-definitions.sql

Or you can export single table in a database.

mysqldump -uroot -ppassword --no-data databasename tablename > table-definitions.sql

Another command I used frequently is the create index. When data grows, the index is a must to keep the fast query.

Create index on different type of column needs different syntax. For a text field, the prefix length must be given.

create index urlindex on post ( url(300) ) ; 

Create index on a text column "url", the number specify how long the prefix of the field will be indexed.

For varchar field, the prefix length is optional, when not specified , the whole field is indexed.

    create index catindex on post ( category() ) ; 

Usually the date field will be used to sort the result, so the field should be indexed.

create index insertdateindex on post ( insertdate ) ; 

The index on date don't accept prefix, the whole field must be indexed.