MariaDB sorting issue
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?
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.
Answer Answered by Ian Gilfillan in this comment.
Try posting the question on #maria on IRC - you can probably get a quicker response there, and can respond to questions for more information immediately.