Importing data from CSV File

Here is an example to importing data from csv file into MySQL table.

LOAD DATA INFILE "/home/mysql/data/file_name.csv" INTO TABLE table_name FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';

Find duplicate repords in Table

Here is an example to find duplicate records from the table.

select address, count(address) as cnt from mailing_list group by address having cnt > 1 order by cnt;

Export a table into csv format

Here is an example to export data from MySQL into CSV File Format.

SELECT * INTO OUTFILE '/tmp/file_name.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM table_name;

Fields Other than the PK Need to be Indexed

The Table Primary Key (PK) is automatically an index.

Indexes should be used whenever a relationship needs to be established between two tables using a field other than the PK E.g. both fields included in the ON table1.field1=table2.field3 clause. Making both fields indexes allows MySQL to JOIN the two tables much more efficiently and much faster.

The working of an index is hidden from the user but involves MySQL setting up lookup pointers.