Category Archives: Database

Database optimisation tips and techniques

Optimise your database design for speed and efficiency – Part 1

By | April 10, 2013

Database schemas Databases are present in almost all kinds of application that need to store information in some form or the other. Web applications like blogs, cms, social networking sites or business applications etc all have a database along with the code. The database design or schema determines how the tables and their relationships are… Read More »

Export table to csv in mysql

By | March 12, 2013

It is possible to export the contents of a table directly to csv format from within mysql. It is useful when migrating large tables. CSV export import is much faster for large tables compared to mysqldump/mysql command. To generate csv from the table use the following command [sql] SELECT id,name INTO OUTFILE ‘/tmp/result.csv’ FIELDS TERMINATED… Read More »

List foreign keys in mysql

By | September 5, 2009

information_schema The following query will list out the foreign keys in mysql. It finds it out from the information_schema database. [sql] select concat(table_name, ‘.’, column_name) as ‘foreign key’, concat(referenced_table_name, ‘.’, referenced_column_name) as ‘references’ from information_schema.key_column_usage where referenced_table_name is not null; [/sql] The output is a clean table listing out all foreign keys from all databases… Read More »