I have users and their payments (ok/fail) and need to select all users that have last payment failed (by date). Mariadb 10+.
That's schema and how i've tried to solve this, but definitely wrong: http://sqlfiddle.com/#!9/99e867d/6/0
Result should be only user id 20 because user 10 has last payment OK. And last payment of user 10 should be OK from 2020-04-04, not from 2020-03-03 as for now.
Thanks for the answer, i wrote query like this and it works as expected now:
SELECT t.user_id FROM ( SELECT p.user_id AS user_id , p.status AS last_status , ROW_NUMBER() OVER( PARTITION BY p.user_id ORDER BY p.created DESC ) AS rownum FROM `payment` AS p ) AS t WHERE t.rownum = 1 AND t.last_status = 'FAIL';
But execution times are started to be bad (up to 5-6 seconds) when i apply this to tables with 1m+ records.
id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 1206883 100.00 Using where; Using filesort 2 DERIVED p ALL NULL NULL NULL NULL 1206883 100.00 Using where; Using temporary
I already have index on payment(user_id), tried to add index payment(user_id,created) but no luck – execution plan is the same.
Any suggestions how to improve this?