Fulltext search order by score and Id (if score the same) is very slow:
SELECT posts.*, MATCH (heading) AGAINST ('+book' IN BOOLEAN MODE) as score
FROM posts
WHERE MATCH (heading) AGAINST ('+book' IN BOOLEAN MODE)
ORDER BY score DESC, id DESC;
But if I remove ordering by primary key, the performance is much better:
SELECT *
FROM posts
WHERE MATCH (heading) AGAINST ('+book' IN BOOLEAN MODE);
As I understand, it sorts by score and ID asc by default
Not only score but freshness is also important, this is why If the score is the same, the newer post should rank first.
Update Someone marked the question as duplicate. But It is not. Another post required an order by ID only. My question is order by score and ID if the scores are the same