The issue I see with searching by slugs is thats its slower than searching by integer, and its more likely to be entered incorrectly by users when typing a url.
You also can not change the slugging algorithm if you want to keep bookmarks valid
You also can not change the slugging algorithm if you want to keep bookmarks valid
The algorithm flexibility might not be a big problem either. The old slugs are stored in the database. Update the algorithm, new slugs adhere to the new algorithm, old ones unchanged, everything still works.
2) There is already an index on ids assuming they are used as primary keys, why adding another index (on text)?
3) To support changing slugs (titles) you will have to keep all the old slugs as well (to redirect them to their new versions). With ids you don't have to do this - ids have no reason to change.
In the end you might not feel the difference in execution time, but hardware requirements for servers...
I agree that they are annoying, but when the only reason against them is to improve URLs aesthetics (assuming they don't pose a security/information disclosure risk) - the trade of performance, hardware requirements and extra code needed is not worth it IMHO.
Which basically means a join in the database. While not terribly expensive, its a lot more involved than a simple column value check.