I want to have WordPress-like URLs for my posts in my tiny CMS. I can do the route easy and get URLs like this:
<year>/<month>/<day>/<slug>
2012/01/31/some-post-slug
So from that I would get 4 pieces of information: year, month, day and the slug. In my database I have a datetime column and a column for the slug:
published: 2012-01-31 01:02:03
slug: some-post-slug
How do I create a good database index and a good query, using my 4 pieces of information, to find posts efficiently?
Note: The Kohana 3 tag is just if someone has some specific tips for that, since that's what I'm using. Still interested in PHP/MySQL answers as well.
Make another column called permalink and put computed value ("2012/01/31/some-post-slug") there. Put an index on it and you're good to go.
It's as efficient (and simple) as it can get. :-)
Generate your urls using article ID (<year>/<month>/<day>/<id>-<slug> ), like 2012/01/31/123-some-post-slug.
PS. Just another way, not so good as Sergio's ;)