Page 1 of 1

Limits to MySQL's LIKE command?

Posted: Thu Oct 23, 2008 1:23 pm
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?

Re: Limits to MySQL's LIKE command?

Posted: Thu Oct 23, 2008 1:49 pm
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).

Re: Limits to MySQL's LIKE command?

Posted: Thu Oct 23, 2008 1:52 pm
by VladSun

Re: Limits to MySQL's LIKE command?

Posted: Thu Oct 23, 2008 1:59 pm
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.

Re: Limits to MySQL's LIKE command?

Posted: Thu Oct 23, 2008 2:06 pm
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?

Re: Limits to MySQL's LIKE command?

Posted: Thu Oct 23, 2008 3:55 pm
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.

Re: Limits to MySQL's LIKE command?

Posted: Thu Oct 23, 2008 5:23 pm
by VladSun
Whenever you can you must refer to a record by its primary key (usually "id" field of integer type).