MySQL slow query log: huge query time, no lock time, minuscule number of rows scanned
I'm going out on a limb here, but did you do an 'EXPLAIN' on this query?

If not, run "EXPLAIN" to see what the execution plan is. This will give you hints on optimization or indexing.

E.g. SOMETHING LIKE

EXPLAIN SELECT a.field1,b.field1 FROM tableA a LEFT JOIN tableB b ON b.a_id = a.id;


The result will be a nicely formatted report.

You may find there's unnecessary complexity in the query that can be pushed to the application or just factored out.