MySQL Fanboy

'Tables' Tag

What is this MySQL file used for?

February 16, 2011

MySQL keeps many different files, some contain real data, some contain meta data. Witch ones are important? Witch can your throw away? This is my attempt to create a quick reference of all the files used by MySQL, whats in them, what can you do if they are missing, what can you do with them. [...]

posted in Tips & Tricks by mark
Federated Tables

July 7, 2010

Your searching for how to create a join across two databases on two different servers and it can’t be done directly. select d1.a, d2.b from db1@server1 join db2@server2 where db1.c = db2.c; does not work. You learn about federated databases. The federated storage engine allows accesses data in tables of remote databases. Now how do [...]

posted in Tips & Tricks by mark
mg_hot_replace_table.pl

May 1, 2010

Do you have MyISAM tables you reload with new data? Do your queries, using that table, get blocked because the table is locked? Do the waiting queries create idle connections slowing down the table load? Do you wish you could just replace the table? Years ago I was told you can replace CSV tables by [...]

posted in Code by admin
Loading Bulk CSV Tables

March 23, 2010

In my job I use many data tables that are transient. New weather data is received all the time and old data is purged. Most of these table are received as CSV files. The data is then loaded into MySQL tables and indexed to be used with geographic queries. Most of these tables never see [...]

posted in Tips & Tricks by mark
Find Abandoned Tables

March 2, 2010

Some times it’s the simple function that helps. This simple query will give return tables that haven’t be updated in over a year. They are most likely. Each should be reviewed to be dropped or converted into a compressed archive table. USE information_schema; SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_ROWS, CREATE_TIME, UPDATE_TIME FROM `TABLES` WHERE (TABLE_SCHEMA != “information_schema”) [...]

posted in Commentary by mark