MySQL SELECT with LIKE expensive?

Question

The next question is about the speed between choosing an exact match (example: INT) and matching "LIKE" with varchar.

Is there any difference? The main reason I ask about this is because I am trying to decide whether to leave an ID from 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 will experience any performance issues in the long run? Should I store identifiers?

Note:

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

As for the number of pages, let's say I'm about 79,230 and the application. growing fast. For example, say 1640 entries per day

Answer 1

Comparing INT will be faster than comparing strings (varchar). LIKE comparisons are even slower because they include at least one pattern.

How important this is in your application is difficult to say from what you told us. If it is not very intense, i.e. you make gazillions of these comparisons, I would go with clarity for your users.

Another thing to think about: will users always enter a URL? Or will they just use a search engine? These days I'm just looking, not trying to remember the URL. What would make this a problem for me as a user. What do you like? Can you indicate in your application how they access your site?

Answer 2

First of all, I think that it doesn’t matter much anyway, yes, it will be slower, since the LIKE offer involves more work than direct comparison, but the speed on regular sites is insignificant.

This can be easily tested if you were to measure the time taken to complete the request, many examples to help you in this section.

To distract from your question, you must ask yourself if you need to use LIKE for this query because "this-is-an-entry" must be unique, right?

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

Answer 3

The query "SELECT * FROM x WHERE = 391239" will be faster than "SELECT * FROM x WHERE =" some-key ", which in turn will be faster than" SELECT * FROM x WHERE LIKE "% some-key% '"(having wild-cards is not going to make a lot of difference).

How much faster? Two times faster? - quite possibly. Ten times faster? stretching it, but possible. The real questions are: 1) does it matter and 2) if you even use LIKE in the first place.

1) Does it matter I probably would not say. If you really have 391,239+ unique articles / pages - and if you get a comparable level of traffic, then this is probably just one of the many scaling problems you are likely to encounter. However, I guarantee that this is not the case, and therefore you should not worry about millions of page views until you get 1 million and one.

2) If you even use LIKE No. If the title / title of the page / article is part of the "slug" URL, it must be unique. If this is not the case, then you shoot in the foot from the point of view of SEO and write yourself a nightmare for the main note. If the title / name is unique, you can simply use "WHERE title =" some-page "and make sure the header column has a unique index.

Edit

You plan on using LIKE for the URL completely crazy. What happens if someone visits

 yoursite.com/articles/the 

Are you returning a list of all pages starting with "the"? What happens if:

Author A creates

 yoursite.com/articles/stackoverflow-is-massive 

After 2 days, author B creates

 yoursite.com/articles/stackoverflow-is-massively-flawed 

Not only will A be very angry that his article will be welcomed, all the permalinks that may have been sent will be broken, and Google is never going to give your articles a reasonable page rank because the content continues to change and dilute effectively yourself.

Sometimes there is a pretty good reason why you have never seen your amazing new idea / function / invention / save time anywhere before.

Answer 4

INT is much faster.

In the string case, I think you shouldn't select a query with LIKE , but just c = , because you're looking for this-is-an-entry , not for this-is-an-entry-and-something .

Answer 5

There are several things to consider:

The type of search performed in the database will “ search for the index ”, in most cases a single-line search using the index.

This type of exact match operation on a single line is not significantly accelerated using int, not lines, they are basically the same for any practical purpose.

What you can do is the next optimization, finding a database using exact matches (without wildcards) is as fast as using an int index. If there is no match, a fuzzy search (wildcard search) is more expensive, but, on the other hand, less likely and can lead to several results. To achieve the best fit, a ranking form is needed.

pseudo code:

• Finding an Exact Match Using the String: Article Like 'entry'
• if (match found) page displayed
• if (match not found) Wildcard Search
  • If (one matching match is found) the displayed page
  • If (more relevant matches) "You tried to find ... page" appears
  • If (no match), an error page is displayed

Note. remember that fuzzy URLs are not recommended from an SEO point of view because people can link your site with multiple URLs that will share your page rank rather than increase it.

Answer 6

If you put the index in the varchar field, it should be in order (in terms of efficiency), it really depends on how many pages you have. Also you need to be more careful and sanitize the string before preventing sql injections , for example. only allow az, 0-9, -, _, etc. in your request.

I would prefer an integer identifier, since it is faster and safer, changing the format to something nicer: http://mysite.com/article/21-this-is-an-entry.html

Answer 7

As said, the comparison is INT <VARCHAR, and if the table is indexed in the field you are looking for, then this will also help, since the server will not need to create a manual control index on the fly.

One thing that helps confirm your requests for speed and meaning is EXPLAIN . You can use this to show which indexes your query uses, as well as the runtime.

To answer your question, if you can build your system using exact matches in the article ID (that is, INT), then it will be much “easier” than if you were trying to match the entire URL using a LIKE expression. LIKE will obviously work, but I would not want to run large, high traffic on it.