MySQL: fetching all rows for search?
I have a MySQL database table with people names with thousands of rows.
I am coding a search script for this table to display the most similar names stored in the table.
So I thought about selecting ALL rows of the table and then using a FOREACH loop that will call like_text () (a function that returns a percentage) and then only display names in the table that match 60% similarity.
Will my site be too slow if I do this (fetching all rows)? Will my server's bandwidth suffer because of this?
ps: "SOUNDS LIKE" MySQL command doesn't help much in this case
source to share
Let the database do a search.
Take a look at this question, see what you need: How to find similar results and sort by similarity?
source to share
Yes, this will most likely slow down your site, especially as your site grows and multiple users search at the same time.
If possible, use a stored procedure or UDF within the database to perform searches. In addition, even if you do not 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 the index can only be used to find rows that start with F.
source to share