MariaDB sorting issue

Hi,

I have one table with 4 million rows and other table with 1 million rows. When I perform join on both the table and perform the sort on the column which is not main table it is behaving really slow.

I have tested with 8 GB RAM. Whole database fits in memory.

I have also checked with EXPLAIN it is not creating temporary table on disk. How can I get best performance?

e.g query.

SELECT DISTINCT u0_.id AS id_0, a1_.created_at AS created_at_1 FROM user u0_ LEFT JOIN advertise a1_ ON (a1_.user_id = u0_.id) ORDER BY a1_.created_at DESC LIMIT 10;

Also note that all the indexes are properly created so that is not he issue here.