MySQL Hints and Tips

MySQL linking a table to itself.
To link a table to itself you simply have to use alias names on the tables, ie:
select * from table as a left join table as b on a.field=b.otherfield;
et viola you have a table linking to itself.

Retrieve the query to recreate a table
This query is very useful when you just want to quickly copy a table structure from one place to another.
SHOW CREATE TABLE {tablename}

Get the list of tables in a database
I personally don’t use this one very often but when your stuck with the command line to access your database it proves very useful:
SHOW TABLES

Get back wasted space
If like me your host only offers a limited amount of space for you DB this is very useful. When you delete rows from MySQL it doesn’t automatically reclaim the space and instead just marks the row as deleted. Do clean out your tables and reclaim that space simply use this query:
OPTIMIZE TABLE

This entry was posted on Saturday, September 13th, 2008 at 2:15 pm and is filed under Programing. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.