0

I am trying to replace a lot of queries like this with a single query.

SELECT * FROM y WHERE x = a ORDER BY timestamp LIMIT 50
SELECT * FROM y WHERE x = b ORDER by timestamp LIMIT 50
SELECT * FROM y WHERE x = c ORDER by timestamp LIMIT 50
SELECT * FROM y WHERE x = d ORDER by timestamp LIMIT 50
SELECT * FROM y WHERE x = .....
  • This is to display a kanban style column board with 50 ordered results for each column
  • SELET * FROM y ORDER by timestamp would return 100k to 1M rows so too slow to limit in code.
  • I want to get the first 50 results for each value of X (there maybe 100 values of X and therefore currently 100 queries)
  • Order is important
  • It's not clear to me that GROUP BY can help here.
5
  • If a stored procedure is a viable solution, you could create a temporary table, insert the IDs of the ~200 records, then query the table on just the records where IDs exist in the temporary table 🤔
    – matigo
    Sep 30, 2021 at 16:21
  • This is all dynamic, i.e. the actual query will have a range of criteria that change regularly (including the ordering) so I don't think this can help. Sep 30, 2021 at 16:36
  • 1
    Which version of MySQL? Sep 30, 2021 at 16:39
  • Do you have an index on (x, timestamp)? That is far more important than rewriting from 4 queries to 1. Sep 30, 2021 at 18:29
  • @ypercubeᵀᴹ it's not 4 queries (x might have 100 values) typically the query is doing a full table scan (because there are a dozen where clauses not shown here) so its more important to do the query once in a single full table scan instead of lots; but then reduce the number of results sent to the client. Sep 30, 2021 at 19:02

1 Answer 1

3

For a reasonable new version of MySQL you can use a window function:

SELECT *
FROM (
    SELECT y.*, ROW_NUMBER() OVER (PARTITION BY x ORDER BY ts) as rn 
    FROM y
    WHERE x IN (a,b,c,d)
) AS t
WHERE rn <= 50
ORDER BY ts
5
  • This is excellent and exactly what we want from what we can tell. We need to do some performance testing. We have some deployments on Aurora which is now very behind mySQL so that's somewhat problematic! Sep 30, 2021 at 17:33
  • If you are on an older version you can mimic window functions with variables: mysqltutorial.org/mysql-row_number If you look for a definition of an hack you may end up on that page:-) Sep 30, 2021 at 19:24
  • Upvoted, but FWIW there's no estimated date for a version of Aurora that supports MySQL 8.0 features. Sep 30, 2021 at 19:53
  • I would be interested to hear about the speed. I suspect that it will collect lots of rows in the derived table, only to filter out most of them with the rn <= 50.
    – Rick James
    Sep 30, 2021 at 22:10
  • @RickJames it seems like that should be a lot quicker than returning all the results to the client. We are working on some tests :) Oct 1, 2021 at 17:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.