I was working on a web application that requires repetitive search for substrings.

Immagine a situation where you have a table like this:

[term | tags]
money | bag,travel,river
ball | football,tennis,baseball,basketball,snowball

So now, you want to do a query that will say:
money = 3
ball = 5
Notice i am displaying the number of tags for each of the term.

To do this: all i need to do is to say:
select term, ' = ', substr_count(tags,',') from mytable;

Normally, even in MySQL5, there is no such function as substring count.

You can run this query to install the substr_count function i just made:


CREATE FUNCTION substr_count (tag CHAR(20),s CHAR(20))
RETURNS CHAR(50) DETERMINISTIC
RETURN round((LENGTH(tag) - LENGTH(REPLACE(tag,s, "")) / LENGTH
(",");


You only need to run this once in a database. It does not install into every
database on your server (or so i presume).

To delete the function, you need to check the structure of your database
and scroll to the bottom of the page, you should see::


- Routines
Routines
Name Type Return type
substr_count Edit Drop FUNCTION char(50) CHARSET utf8

Try to avoid using mysql functions if php can easily do it, efficient when building a web app

i would have been tempted to use the php explode function on the set of strings. Then pass them into an array and you know what next. . .

So for that kinda scenario, you will first send the query to php eh? process them, then re-run the query again eh?
How about performance issues? If you are working on an application that has to search through thousands of records?

I usually avoid using procedures all these time. The application i was working on - when i did that your method which
i have been doing till now, the query when the database still have like 1000 records took about 20 minutes to complete.

And i now, i am talking of records that are even much more than that. I have crashed some few servers in the past BTW.