Limits to MySQL's LIKE command?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
JAB Creations
DevNet Resident
Posts: 2341
Joined: Thu Jan 13, 2005 6:44 pm
Location: Sarasota Florida
Contact:

Limits to MySQL's LIKE command?

Post by JAB Creations »

One of my current projects has tags and I'm working on programming it to display all the items associated with that tag. In example index.php?tag=rpg and I'm using MySQL's LIKE command to look in the MySQL table for the version with proper name, spaces, casing, etc. I'm curious about what the possible limitations are to using LIKE in this manner or even in different circumstances?
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Limits to MySQL's LIKE command?

Post by VladSun »

I would not use LIKE in a tag system. Instead, I'll create a table of unique tags and a second table for associating an item with a tag (many-to-many).
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Limits to MySQL's LIKE command?

Post by VladSun »

There are 10 types of people in this world, those who understand binary and those who don't
User avatar
JAB Creations
DevNet Resident
Posts: 2341
Joined: Thu Jan 13, 2005 6:44 pm
Location: Sarasota Florida
Contact:

Re: Limits to MySQL's LIKE command?

Post by JAB Creations »

In that case would it simply be wiser to have links to tags using a string replace for spaces with underscores and to leave the letter casing intact? Then all I'd have to do is a string replace for the underscores to spaces and I could look for an exact match instead. I'll just have to ensure underscores aren't allowed when I get to the point of creating tags. The letter casing would be visible in the URL...so perhaps now that I think of it...in the rare circumstance that a user types index.php?tag=rpg I could use like to search for a match if the letter casing was the reason a match wasn't found? I think that's a little excessive though I'm just trying to conceive how LIKE could be useful. I'm doing something like Onion I suppose as far as the list is concerned though this thread isn't necessarily a code thread.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Limits to MySQL's LIKE command?

Post by VladSun »

First to notice - in some cases using LIKE will force MySQL not to use indexes:
http://dev.mysql.com/doc/refman/5.0/en/ ... dexes.html

Second, it depends on your collation whether strings are case sensitive or not:
http://dev.mysql.com/doc/refman/5.0/en/ ... ivity.html
Any collation that ends with a "_ci" is case insensitive.

Third, it's good to have these two tables in a tag system because your DB is normalized.

And last - I could not get the idea of using underscore for spaces. If your are converting ALL underscores to spaces what makes the difference?
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
JAB Creations
DevNet Resident
Posts: 2341
Joined: Thu Jan 13, 2005 6:44 pm
Location: Sarasota Florida
Contact:

Re: Limits to MySQL's LIKE command?

Post by JAB Creations »

Tag: "Role Playing"

To create anchor string replace space with underscore...
Anchor: "index.php?tag=Role_Playing"

When I program the tag page I will reverse the string replace...
"Role_Playing" becomes "Role Playing" again and I can do an exact match.

However I clearly see how...
index.php?tag=RPG
...and...
index.php?tag=6
...can differentiate in query speed. The later simply requires us to choose a specific row versus looking at all the rows! This efficiency would directly correlate to the size of the table! I haven't thought about it in that manner though it's very clear how it's more desirable.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Limits to MySQL's LIKE command?

Post by VladSun »

Whenever you can you must refer to a record by its primary key (usually "id" field of integer type).
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply