MySQL is SELECT with LIKE expensive?

Question

The following question is regarding the speed between selecting an exact match (example: INT) vs a "LIKE" match with a varchar.

Is there much difference? The main reason I'm asking this is because I'm trying to decide if it's a good idea to leave IDs out of my current project.

For example Instead of:

http://mysite.com/article/391239/this-is-an-entry

Change to:

http://mysite.com/article/this-is-an-entry

Do you think I'll experience any performance problems on the long run? Should I keep the ID's?

Note:

I would use LIKE to keep it easier for users to remember. For example, if they write "http://mysite.com/article/this-is-an" it would redirect to the correct.

Regarding the number of pages, lets say I'm around 79,230 and the app. is growing fast. Like lets say 1640 entries per day

Answer 1

An INT comparison will be faster than a string (varchar) comparison. A LIKE comparison is even slower as it involves at least one wildcard.

Whether this is significant in your application is hard to tell from what you've told us. Unless it's really intensive, ie. you're doing gazillions of these comparisons, I'd go with clarity for your users.

Another thing to think about: are users always going to type the URL? Or are they simply going to use a search engine? These days I simply search, rather than try and remember a URL. Which would make this a non-issue for me as a user. What are you users like? Can you tell from your application how they access your site?

Answer 2

Firstly I think it doesn't really matter either way, yes it will be slower as a LIKE clause involves more work than a direct comparison, however the speed is negligible on normal sites.

This can be easily tested if you were to measure the time it took to execute your query, there are plenty of examples to help you in this department.

To move away slighty from your question, you have to ask yourself whether you even need to use a LIKE for this query, because 'this-is-an-entry' should be unique, right?

SELECT id, friendly_url, name, content FROM articles WHERE friendly_url = 'this-is-an-article';