Searching text with SQL

Database with various text fields. When user searches the site wish to check 3 of those fields for the matching search term [name, filename, comment]. Basic level just needs to return any rows that match the search term in any of the 3 fields. The fields are all short no more than 256 characters.

What would be the most practical and efficent way to implement this in SQL? Looking for things like datatypes, type of search query(LIKE etc.) or any other ideas like a new combined field.

Already have code in and working fine but curious to learn better ways to implement it from programmers stronger at SQL.

Assuming that the search fields are all varchars of some length, do a query like this, replacing search_term with what you want to search for: select * from your_table where name like '%search_term%' or filename like '%search_term%' or comment like '%search_term%'

>>1
i have a problem... sometimes i get randy thoughts and BOING i'm standing to attention, and this happens probably 2 or 3 times a day.

usually not a problem when i am sat at work or am at home, but obviosly when i am with friends or family or out anywhere in public i have a very obvious "point" to worry about.

women are lucky that they can get all lubed up at any time of day in secret and nobody knows!

but for me, what can i do? i dont know if i'm in the minority or if this happens to all guys becuase my mates just laugh at me when i ask them! is there some kind of anti-viagra pill or something i can take? i've seen stuff i can lock my nob up in like the cb3000 device but it seems a bit pervy for every day wear!

please help me, i can't see a doctor about it because as soon as i do i know that pervy thoughts will enter my head and BOING he'll think i'm after his botty or something! :-(

>>2
Combine them using a spacing character that won't appear in the search term

select * from your_table where name+'巴'+filename+'巴'+comment like '%search_term%'

>>1
If you're using the piece of shit known as MySQL, use fulltext indexes.

http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html

>>4
>>5
Once a row is added the data in the fields are unlikely to change often if at all. How about adding another field onto the table and fill it with name+filename+comment and put fulltext index on that field. Would that be significantly faster than searching over the 3 fulltext indexed fields to justify the increase in database size?

>>6
No, it would be pointlessly redundant. The full text search tokenizes the fields and puts them in an auxillary data store anyway.

巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴巴

巴

  用 用 用       巴 ．．．．．．．．

>>7
Ahh that makes alot of sense, will go with the fulltext index over the 3 columns then. Thanks for your help.

>>5
>http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html
>Full-text indexes can be used only with MyISAM tables

OH LAWD
Enjoy your data corruption

>>12
I will, along with my job.

>>5
http://www.postgresql.org/docs/current/static/textsearch.html

Fulltext searches are standardized and available in any RDBMS that isn't shitty. MySQL basically disregarded the standard and just did fuck all (which is unsurprising), but you can get pretty much the same features with anything else.

A fulltext index is definitely what you want to do. >>2 is going to be slow as fuck, even if you keep a column to cache the concatenation of all three normalized columns you want to search. It'll work fine with small datasets (<50k rows) but anything bigger than that and you'll start shitting bricks (especially if you're using TEXT fields).

As a final note about using a fulltext search over multiple columns with MySQL (since no one uses PostGRES) -- use a single MATCH AGAINST statement and try not to mix it with ORDER BY, LIMIT or OFFSET. It's a pain in the ass, but you should try to avoid WHERE in there too (unavoidable in some situations).

Fp putchar getc fp.

All work and no play makes Jack a dull boy
 All work and no play makes Jack a dull boy
 All work and no play makes Jack a dull boy
 All work and no play makes Jack a dull boy
 All work and no play makes Jack a dull boy
 All work and no play makes Jack a dull boy
 All work and no play makes Jack a dull boy
 All work and no play makes Jack a dull boy
 All work and no play makes Jack a dull boy
All work and no play makes Jack a dull boy
 All work and no play makes Jack a dull boy
 All work and no play makes Jack a dull boy
 All work and no play makes Jack a dull boy
 All work and no play makes Jack a dull boy
 All work and no play makes Jack a dull boy
 All work and no play makes Jack a dull boy
 All work and no play makes Jack a dull boy
 All work and no play makes Jack a dull boy

bampu pantsu