MySQL: Fetching all rows for searching?

Question

I have a MySQL Database table with peoples names with thousands of rows.

I'm coding a search script for this table to display the most similiar names stored in the table.

So I thought of fetching ALL the rows of the table, then using a FOREACH loop that will call similar_text() (a function that returns a percentage number) and then display on the table only the names that matches 60% of similarity.

Will my website performance slow too much if do this (fetching all rows)? Will my server bandwidth suffer because of that?

ps: 'SOUNDS LIKE' MySQL command doesn't help much on this case

What does the similar_text do? can you post the codes so we can see if it can be shifted to the database to do processing? — iWantSimpleLife, Mar 16 '12 at 01:12

score 2 · Answer 1 · edited May 23 '17 at 12:04

2

Let the database do the searching.

See this question, looks like what you need: How to find similar results and sort by similarity?

edited May 23 '17 at 12:04

Community

1
1

answered Mar 15 '12 at 23:01

The Nail

8,355
2
35
48

score 1 · Answer 2 · answered Mar 15 '12 at 22:57

Yes this will most likely slow down your site, especially as your site grows and you have many users searching simultaneously.

If possible use a stored procedure or user defined function inside the database to do the searching. Also even if you don't know the exact spelling of the entry you are looking for, if you know the first letter you can speed up the search. You can use something like WHERE name LIKE 'F%' AND similar_text(name, 'FOOBAR') > 0.6 because then an index can be used to find only those rows that start with F.

Did you use similar_text() in a MySQL statement? – user1091856 Mar 15 '12 at 23:09 — user1091856, Mar 15 '12 at 23:09

MySQL: Fetching all rows for searching?

2 Answers2