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 Solution

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.

Related Solutions

Mysql – concatenate multiple MySQL rows into one field

You can use GROUP_CONCAT:

SELECT person_id,
   GROUP_CONCAT(hobbies SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;

As Ludwig stated in his comment, you can add the DISTINCT operator to avoid duplicates:

SELECT person_id,
   GROUP_CONCAT(DISTINCT hobbies SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;

As Jan stated in their comment, you can also sort the values before imploding it using ORDER BY:

SELECT person_id, 
       GROUP_CONCAT(hobbies ORDER BY hobbies ASC SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;

As Dag stated in his comment, there is a 1024 byte limit on the result. To solve this, run this query before your query:

SET group_concat_max_len = 2048;

Of course, you can change 2048 according to your needs. To calculate and assign the value:

SET group_concat_max_len = CAST(
                     (SELECT SUM(LENGTH(hobbies)) + COUNT(*) * LENGTH(', ')
                           FROM peoples_hobbies
                           GROUP BY person_id) AS UNSIGNED);

Mysql – How to output MySQL query results in CSV format

From Save MySQL query results into a text or CSV file:

SELECT order_id,product_name,qty
FROM orders
WHERE foo = 'bar'
INTO OUTFILE '/var/lib/mysql-files/orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Note: That syntax may need to be reordered to

SELECT order_id,product_name,qty
INTO OUTFILE '/var/lib/mysql-files/orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM orders
WHERE foo = 'bar';

in more recent versions of MySQL.

Using this command, columns names will not be exported.

Also note that /var/lib/mysql-files/orders.csv will be on the server that is running MySQL. The user that the MySQL process is running under must have permissions to write to the directory chosen, or the command will fail.

If you want to write output to your local machine from a remote server (especially a hosted or virtualize machine such as Heroku or Amazon RDS), this solution is not suitable.

Best Solution

Related Solutions

Mysql – concatenate multiple MySQL rows into one field

Mysql – How to output MySQL query results in CSV format

Related Question