Php – Mysql Sub Select Query Optimization

MySQLoptimizationPHPsqlsubquery

I'm running a query daily to compile stats – but it seems really inefficient. This is the Query:

SELECT a.id, tstamp, label_id, (SELECT author_id FROM b WHERE b.tid = a.id ORDER BY b.tstamp DESC LIMIT 1) AS author_id
FROM a, b
WHERE (status = '2' OR status = '3') 
AND category != 6
AND a.id = b.tid
AND (b.type = 'C' OR b.type = 'R')
AND a.tstamp1 BETWEEN {$timestamp_start} AND {$timestamp_end}
ORDER BY b.tstamp DESC
LIMIT 500

This query seems to run really slow. Apologies for the crap naming – I've been asked to not reveal the actual table names.

The reason there is a sub select is because the outer select gets one row from the table a and it gets a row from table b. But also need to know the latest author_id from table b as well, so I run a subselect to return that one. I don't want to run another select inside a php loop – as that is also inefficient.

It works correctly – I just need to find a much faster way of getting this data set.

Best Answer

If b.tstamp is unique within b.tid, take OMG Ponies' solution.

Otherwise you could try this solution. It sorts the whole result by b.tstamp DESC and adds a ranking per author_id. The outer selects takes only the row with rank = 1, which is the one with the greatest tstamp per author_id.

SELECT id, tstamp, label_id, author_id
  FROM (SELECT id,
               tstamp,
               label_id,
               author_id,
               CASE
                 WHEN @author_id != author_id THEN @row_num := 1 
                 ELSE @row_num := @row_num + 1
               END AS rank,
               @author_id := b.author_id
          FROM a,
               b,
               (SELECT @row_num := 0, @author_id := NULL) y
          WHERE a.id = b.tid
          AND (status = '2' OR status = '3') 
          AND category != 6
          AND (b.type = 'C' OR b.type = 'R')
          AND a.tstamp1 BETWEEN {$timestamp_start} AND {$timestamp_end}
          ORDER BY b.author_id, b.tstamp DESC
  ) x
 WHERE x.rank = 1
LIMIT 500

I have not tried it, so please comment if it does not work.