searching a mysql db

[Anon]

Hey,

I'm looking for a way to search within a table in my mysql database.

Can anybody help me?

Thanks!

Coen

[Anon]

SELECT * FROM table WHERE name LIKE '%john%'

Like that?

---John Holmes...

[Anon]

kind of.

This searches one field, I want to search an entire table, without using a lot of \"LIKE\".

But thanks for the swift reply!

[Anon]

There are ways to do it, but it's not very efficient. The best thing to do is create another table with the words and an id number relating back to a row in the main table. Whenever you insert anything into the main table, split up all of the words and insert every word into the words table. Then when you do your searches, you search on the keywords, find the id number, then load up that row. Something like that...

The inefficient way would be to

select * from your_table where concat(field1,field2,field3,field4,...) like '%john%';

---John Holmes...

[Anon]

There is a column about this kind of searches in the columns section of this site.

It works like a charm, but it is very inefficient to store every word in a seperate row in the search table.

In the end, all you do is count how many times a word occurs for a given ID.

I changed it a little so I use the procedure as described in the column, but store the data in a temporary table.
Then I run a query that counts all the words for each ID in that temporary table,
so I get a list of words and how many times they occur for each ID.

It's avery small change, but it saves k's and k's of records in your search table, which ultimately speds up your search quite a bit.

Also, as someone else (I forget his/her name, sorry) mentioned, it is worth a try to add an index to the search table. Could speed up things quite a bit.