Optimization of SQL code

Question

How can I optimize the following code to not use 3 Selects and not plaster the query to only 3 status

(
    SELECT * 
    FROM historico 
    WHERE 
        his_status = 'FRACASSO' 
    ORDER BY his_data DESC 
    LIMIT 50
)
UNION ALL
(
    SELECT * 
    FROM historico 
    WHERE 
        his_status = 'REAVALIAR' 
    ORDER BY his_data DESC 
    LIMIT 50
)
UNION ALL
(
    SELECT * 
    FROM historico 
    WHERE 
        his_status = 'SUCESSO' 
    ORDER BY his_data DESC 
    LIMIT 50
)

At the moment I have 2562 records in the historical table and I am using them all to train an RNA. It costs 3 to 4 minutes. This query will allow the user to indicate how many samples of each status you want to use. Quantity 50 is an example. Query optimization is also to decrease the cost of time.

Answer 1

You can do it this way. It will only return records with "line" less than or equal to 50 (ie, 50 records max. of each type):

SELECT *
FROM 
(SELECT *,
    (@numero:=IF(@status = `his_status`,
     @numero+1,
     IF(@status := `his_status`, 1, 1))) linha 
  FROM historico a
  CROSS JOIN (SELECT @numero:=0) b
  ORDER BY his_status, his_data DESC
) AS c 
WHERE c.linha <= 50;

Sqlfiddle by friend’s indication @Everson in the comments: http://sqlfiddle.com/#! 9/e6166e/5/0

Answer 2

Here is an alternative capable of bringing up to 50 records of each status sorted by date. In case of a tie between two records the result is unpacked by id (the smallest id prevails).

SELECT h.*
FROM historico AS h LEFT JOIN historico AS h2
    ON h.his_status = h2.his_status 
       AND (h.his_data < h2.his_data OR (h.his_data = h2.his_data AND h.id > h2.id))
WHERE h.his_status IN ('FRACASSO', 'REAVALIAR', 'SUCESSO')
GROUP BY h.id
HAVING COUNT(*) < 50
ORDER BY h.his_status, h.his_data DESC, h.id;

See working on DB Fiddle

This version is more compact. You can filter how many status want. You can even remove the clause where to return the first 50 results of all status of your table.

I can’t compare the performance of this solution with the original version with UNION ALL. This has to be done with your dataset complete, with the exact same table and index structure, in the same version of Mysql with the same settings. That said the link above shows the execution plan for a small amount of data in the absence of any index other than that created implicitly by PK, using Mariadb 10.2. Under these conditions the original version is scanning the full table for each status desired. The above version, although it also scans the full table twice and uses a temporary table, does so regardless of the amount of status in the filter.