Limits to MySQL's LIKE command?
Moderator: General Moderators
- JAB Creations
- DevNet Resident
- Posts: 2341
- Joined: Thu Jan 13, 2005 6:44 pm
- Location: Sarasota Florida
- Contact:
Limits to MySQL's LIKE command?
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?
Re: Limits to MySQL's LIKE command?
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
Re: Limits to MySQL's LIKE command?
There are 10 types of people in this world, those who understand binary and those who don't
- 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?
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.
Re: Limits to MySQL's LIKE command?
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?
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
- 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?
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.
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.
Re: Limits to MySQL's LIKE command?
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