How can I improve better performance of insert statement?

[Sina]

Hello every one;

I'm transfering over milion records to a table in mysql and wanted to know what is the best way to improve the rate at which the data is being pumped in?

So how can I write a better insert statements in general?

Thank you all.

 

[unixrobot]

One of the things you need to do is while using NUMERIC data in your INSERT statements make sure to use single quotes ' and not double quotes " when descibing a record.
Same thing applies when you are trying to search for data in the large database.
For example:

INSERT INTO <database_name> VALUES '7323823','someone@anywhere.com')

Another thing to keep in mind is that nothing is faster than what MySQL natively does, so import several records first, then export them with mysqldump and look how MySQL did it, use the same structure to import.

If you are importing into an existing database, make sure all tables in the database are already ANALYZED and OPTIMIZED. To analyze/optimize tables:

1) Login to the database (mysql -u<user> -p<password>)
2) Select database you want to use (use <database>)
3) See what tables are inside that database (describe <database>)
4) optimize table <table_name>;
5) analyze table <table_name>;