How can I INSERT 1 million entries to my MySQL DB?

I wa开发者_C百科nt to test the speed of my SQL queries (update queries) with a real "load" on my DB. I'm relatively fresh to DB's and I am doing more complex queries than I have before, and I'm getting scared by people talking about performance like "30 seconds for 3000 records to be updated" etc. So I want to have a concrete experiment showing what my performance will be in production.

To achieve this, I want to add 10k, 100k, 1M, 10M records to my DB and then run my query.

My issue is, how can I do this? I have a "name" primary key field that must be unique and be <= 15 characters and have alphanumeric entry. The other fields I want to be the same for all created entries (i.e. a "foo" field I want to start at 10000)

If there's a way to do this and get approximately 1M entries (i.e. could be name collisions) that's fine. I'm just looking for a benchmarking dataset.

If there's a better way to benchmark my query, I'm all ears. I'm planning to simply execute and see how long the query says it takes.

Edit: It's worth noting that this is for a server and has nothing to do with "The Web" so I don't have access to PHP. I'm seeing some PHP scripts to populate, is there perhaps a way to have a perl script write out all these queries and then suck them in to the command line mysql tools?

I'm not sure of how to use just MySQL to accomplish this, but if you have access to PHP, then use this:

<?php

    $start = time();
    $interval = 10000000; // 10M
    $con = mysql_connect( 'server', 'user', 'pass' );
    mysql_select_db( 'database' );

    for ( $i = 0; $i < $interval; $i++ )
    {

        mysql_query( 'INSERT INTO TABLE (fields) VALUES (values)', $con );

    }

    $endt = time();
    $diff = ( $endt - $start );
    print( "{$interval} queries took " . date( 'g:i:s', $diff ) . " to execute." );

?>

If you want to optimize querys you should look into the EXPLAIN statement of MySQL. To populate your database I would suggest you write your own litte PHP script or check out this one http://www.generatedata.com

Regarding your edit: you could generate a big text file with perl and then use the MySQL CLI to load the file into the table, for more info please see: http://dev.mysql.com/doc/refman/5.0/en/loading-tables.html

You just want to prepopulate your database so that you have something to run your queries against, and you are not benchmarking the initial insertion process?

In that case, just generate your input data as a tab-delimited file and use mysqlimport to populate your database.