The ranking functions combined that top now can take a dynamic parameter helps a lot.
Some of the read/shared locks can also be eliminated from SQL Server by doing interesting things.
I think for a small web app and smaller databases, MySQL might be a good choice. If you want to grow this into anything bigger and use any MS add-ons ur going to be sorry for making that decision down the line.
The usage of LIMIT has always been a big attraction for me as well.It makes everything so easy in doing paging, no need to define a page size or anything like that, just pass two numbers at the end of the query and you are ready to go. Speed (if you just need read) is as well a very good argument.
LIMIT It's not the same as using TOP 10 That gives you just the first ten results but them you will need the results from 11 to 20 and 21 to 30 etc. There is a way of using top and subqueries to filter the results and achieve a similar result but in that case use pages, it's better.