Category : "Database"

Database optimisation tips and techniques

Optimise your database design for speed and efficiency – Part 1

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 constructed. Database design is a crucial ...

Export table to csv in mysql

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 SELECT id,name INTO OUTFILE '/tmp/result.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY ...

Mysql autoincrement column that resets every month

There are situations when we need an autoincrement field to reset every month or so. Example : Invoice Numbers : Month of october 2011-10-01 2011-10-02 ... ... ... 2011-10-800 Month of november 2011-11-01 2011-11-02 ... ... and so on. Groupwise autoincrement P1 , P2 , Q1 , Q2 , Q3 , Q4 , P3 , R1 , P4 ... Myisam table has a feature where , if a primary key contains 2 columns where one is an autoincrement ...

List foreign keys in mysql

information_schema The following query will list out the foreign keys in mysql. It finds it out from the information_schema database. 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; The output is a clean table listing out all foreign keys ...

Create foreign key using Phpmyadmin

The innodb storage engine supports foreign keys in Mysql. To create foreign keys in phpmyadmin : 1. Convert both tables into innodb, if they are not already. 2. View the structure of the table which will have a foreign key. Make the referencing field an INDEX. 3. Now come back to structure view and click Relation view. In the Relation view page the field (which ...

Import mysql dump files with view definer and sql security

Views and mysqldump A little back I tried to backup a database from my webhost and restore it on my localhost mysql. It had a few views. The import of the sql file seemed to work fine. But on accessing the views in phpmyadmin mysql gave the error : #1449 - There is no 'projects'@'localhost' registered The problem is with a security feature ...

Create AutoIncrement column/field in Apache Derby

While creating a table a particular column / field can be made autoincrement as : CREATE TABLE students ( id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), name VARCHAR(24) NOT NULL, address VARCHAR(1024), CONSTRAINT primary_key PRIMARY KEY (id) ) ; The value of an autoincrement column increments automatically with every insert and doesnt need to be specified in the insert command. It ...