How to find slower MySQL queries from many small queries

I'm wondering if anyone has a suggestion for m开发者_高级运维y situation:

I have a process that runs many tens of thousands of queries. The whole process takes between 5 and 10 minutes. I want to know which queries are running slower than the rest, but I know that none of them are running for more than say, 5 seconds (with this many queries, that would be very noticeable in my logs). How should I find out which ones are taking the most time, and are the ones that, if optimized, would provide the best results?

MORE DETAILS:

My queries run single-threaded and synchronous, and I'd say 70% SELECT and 30% INSERT/UPDATE. I'd have to get some heads together and determine if the work can be split up into different units that can be run simultaneously - I'm not sure...

All the queries are either simple INSERT statements, single-property UPDATE statements, or SELECT statements on either a primary or foreign key or a two-field ANDed restriction.

DESCRIPTION OF THE ISSUE:

What I'm doing is basically copying a complex directed graph structure, in its entirety. Nodes are database entries, and adjacencies represent essentially foreign keys, but not strictly-speaking (they could be a two-field combination, where the first says what table the second is the id for).

---

Take a look at MySQL's slow query log. You can configure the threshold of what is regarded as "slow".

---

Basically, you track time from query start to query end, e.g.:

function getmicrotime() {
    list($usec, $sec) = explode(" ",microtime());
    return ((float)$usec + (float)$sec);
}

$query_start = getmicrotime();
$query = 'SELECT ...';
mysql_query($query, $connect); 
$query_end = getmicrotime();
if ($query_end - $query_start > 2) { 
    // add query to log
    your_slow_queries_logger($query);
}

I'd sugest to make some kind of a wrapper for mysql_query function that would take care of logging slow queries.

I, personally, log my slow queries using syslog

---

If you serious in optimizing MySQL, have a read at this book

High Performance MySQL: Optimization, Backups, Replication, and More

Some of the contents may be outdated for the newest MySQL versions, but should be sufficient as it covers MySQL 5.1. It also has an extensive chapter on benchmarking along with techniques and methods and guiding you to create a benchmark plan that suits your needs.

It also has chapters on Index and Query optimizations which would be very helpful to you if you need to optimize the slow queries identified with the benchmarks.