Levenshtein Based Search Suggest

Ok -

Here is a situational problem. I want to implement a search query using
the Levenshtein algorithm to suggest what the user "might" have meant
(those suggestions already being entries in the database). Simple, just
create a user defined function, and run the thing purely in MySQL.

Problem solved, right?
Wrong.

My client's host is running MySQL version 4.1.21, which pre-dates the
user defined function capability. Doh. So the only viable option I see
is to select all the possible values from the database, create a php
array with these values and run the built in php Levenshtein function
against them all. This seems wasteful for something that will be so
frequently used.

Is there anyway I can relegate the Levenshtein algorithm back to MySQL
from whence it came, without the use of user defined functions?

Thanks,

-- whit

Whit wrote:
> My client's host is running MySQL version 4.1.21, which pre-dates the
> user defined function capability. Doh.

I think you have confused user defined functions with stored procedures.
UDF's are supported in MySQL all the way back -- certainly they are
supported in 4.1.
See http://dev.mysql.com/doc/refman/4.1/en/adding-udf.html

You must write the functions in some compiled language, like C/C++. You
can't write the function in the procedural SQL language, as you would
with triggers and stored procedures in MySQL 5.0.

Regards,
Bill K.

Whit wrote:
> Is there anyway I can relegate the Levenshtein algorithm back to MySQL
> from whence it came, without the use of user defined functions?

Funny coincidence, I posted about making a Levenshtein UDF just a few
days ago. If your target platform is Win32, perhaps I can send the DLL
to you once I finish it.

Cheers,
Nicholas Sherlock

--
http://www.sherlocksoftware.org

Please forgive me, I am quite new to all this. After close examination
and some manual-reading, I did in fact mean a stored procedure. A user
defined function would certainly work, but I running on a host that I
don't have control over, so that technique might prove problematic.

I've spoken with my host and they said they would be willing to move me
to a server with MySQL5. So turns out I can do stored proc's. Now I
just have to learn them, then implement Levenshtein. Anyone have any
slick tutorials that can walk me through strored procedures?

Thanks,

-- whit

p.s. Nicholas, I am running on a *nix server, but even if I wasn't, I
think learning stored procs will be good for me. If, er, ah, WHEN I can
figure out how to do it I would be happy to send you the proc. :)

Nicholas Sherlock wrote:
> Whit wrote:
> > Is there anyway I can relegate the Levenshtein algorithm back to MySQL
> > from whence it came, without the use of user defined functions?
>
> Funny coincidence, I posted about making a Levenshtein UDF just a few
> days ago. If your target platform is Win32, perhaps I can send the DLL
> to you once I finish it.
>
> Cheers,
> Nicholas Sherlock
>
> --
> http://www.sherlocksoftware.org

How do I create the 2-d array to maintain the results of the dynamic
algorithm? I can't seem to find an array structure in mysql procedures
....

-- whit

Bill Karwin wrote:
> Whit wrote:
> > My client's host is running MySQL version 4.1.21, which pre-dates the
> > user defined function capability. Doh.
>
> I think you have confused user defined functions with stored procedures.
> UDF's are supported in MySQL all the way back -- certainly they are
> supported in 4.1.
> See http://dev.mysql.com/doc/refman/4.1/en/adding-udf.html
>
> You must write the functions in some compiled language, like C/C++. You
> can't write the function in the procedural SQL language, as you would
> with triggers and stored procedures in MySQL 5.0.
>
> Regards,
> Bill K.

Whit wrote:
> How do I create the 2-d array to maintain the results of the dynamic
> algorithm? I can't seem to find an array structure in mysql procedures

Right, there is no array datatype in MySQL.

I really don't think MySQL stored procedures are the best solution for
this task. The stored procedure language is a pretty lackluster
language for general purpose tasks.

Personally, I never use stored procs.

Regards,
Bill K.

A user defined function would probably fit the bill ... but if I can't
use these because of my host ... Are there any alternatives to using
php to do the grunt work?

-- whit

Bill Karwin wrote:
> Whit wrote:
> > How do I create the 2-d array to maintain the results of the dynamic
> > algorithm? I can't seem to find an array structure in mysql procedures
>
> Right, there is no array datatype in MySQL.
>
> I really don't think MySQL stored procedures are the best solution for
> this task. The stored procedure language is a pretty lackluster
> language for general purpose tasks.
>
> Personally, I never use stored procs.
>
> Regards,
> Bill K.

I wish I had an option, but I think a stored procedure is my only
choice. I know it shant be pretty, but the alternative is a huge
data-dump from mysql into a php script that does the work. I think not.

So how could I implement a 2-d array in a stored proc? Temp table?

Thanks for your consideration everybody,

-- whit


Bill Karwin wrote:
> Whit wrote:
> > How do I create the 2-d array to maintain the results of the dynamic
> > algorithm? I can't seem to find an array structure in mysql procedures
>
> Right, there is no array datatype in MySQL.
>
> I really don't think MySQL stored procedures are the best solution for
> this task. The stored procedure language is a pretty lackluster
> language for general purpose tasks.
>
> Personally, I never use stored procs.
>
> Regards,
> Bill K.

Whit wrote:
> So how could I implement a 2-d array in a stored proc? Temp table?

Note that you only need two rows in Levenshtein, so if you have 1D
arrays, you should be fine.

Cheers,
Nicholas Sherlock

--
http://www.sherlocksoftware.org