visitor (0 QPoints)
  • FR
  • EN
  • NL
  • DE
  • ES
315 experts, 1193 registered users, 1659 questions already answered
European Experts Exchange, the very best site for high-quality IT solutions

New Improved Search!

 


05/10/2011 1h30 : Steve Jobs is dead, the father of Apple ][ is gone, we are all orphaned.

Databases :: MySql :: What data type should I use
By: jayrod U.S.A.  Date: 21/04/2003 00:00:00  English  Points: 50 Status: Answered
Quality : Excellent
I've got a script that will import email text into a database. right now i'm just using a varchar255 field to hold it. What I'd like to know is there a better data format to use? one that will allow me to perform like searches on?

I'm hoping to store it in a huge field and do something like this.


select * from table where email_body like %$some_string%

or where table contains some combination of strings.
By: TheFalklands Date: 21/04/2003 13:03:00 English  Type : Comment
I think the best fiend type is text.(Cos that you may need to save more than 255 characters).
By: VGR Date: 21/04/2003 15:31:00 English  Type : Answer
yes, TEXT seems appropriate, BUT you'll need to rely on the infamous FULLTEXT index type, and will be limited to 2+ to 5+ characters-long words

and never search for 'someword' liek this : LIKE '%someword%', because for example searching for 'men' will return 'refreshment', 'amendment', 'women'...
By: jayrod Date: 22/04/2003 20:20:00 English  Type : Comment
actually I'd like the search for men to pull back refreshment. is there any other searching mechanisms I can use?

Thanks
By: VGR Date: 22/04/2003 20:38:00 English  Type : Comment
yes. I'm working on this right now for a client.

You could try a very fast solution by trying this :
select * from yourtable where yourfield LIKE 'men %' or yourfield LIKE '% men' or yourfield LIKE '% men %' or yourfield LIKE '% men\'%' or yourfield LIKE '%\'men %';

don't worry, the expression is evaluated only once ;-)
By: Ice_S5 Date: 22/04/2003 21:32:00 English  Type : Comment
What about:
SELECT * FROM your_table WHERE your_field RLIKE '[[:<:]]men[[:>:]]'

IMHO it works.
By: jayrod Date: 22/04/2003 21:36:00 English  Type : Comment
Ok let me try a few of those queries and I'll grade one as the answer. Thank you for the input
By: VGR Date: 22/04/2003 21:53:00 English  Type : Comment
try to avoid REGEXP call, they are particlularly inefficient 8-)

on what I work right now :

WHERE REGEXP("...") : 429ms
WHERE CONCAT() LIKE ...(multiple LIKEs) : 47ms
WHERE ... MATCH ('...') [fulltext] : 6ms with proper index
By: jayrod Date: 22/04/2003 21:56:00 English  Type : Comment
explain the match statement... i'm green to database design btw
By: VGR Date: 22/04/2003 22:11:00 English  Type : Comment
ALTER TABLE yourtablename ADD FULLTEXT (field1);

SELECT * FROM yourtable WHERE MATCH(field1) AGAINST('thevalue');

see MySql doc at <A HREF="http://www.mysql.com">www.mysql.com</a>, type in "fulltext" in the search box
By: jayrod Date: 26/04/2003 14:06:00 English  Type : Comment
I went with full text.. thanks

Do register to be able to answer

©2010 These pages are served without commercial sponsorship. (No popup ads, etc...). Bandwidth abuse increases hosting cost forcing sponsorship or shutdown. This server aggressively defends against automated copying for any reason including offline viewing, duplication, etc... Please respect this requirement and DO NOT RIP THIS SITE.
Please DO link to this page! Post to Twitter Post to Plurk Post to Yahoo Buzz Post to Delicious Post to Digg Post to Facebook Post to MySpace Post to Ping.fm Post to Reddit Post to StumbleUpon

EContact
browser fav
page generated in 355.623010 milliseconds

Why Google AdSense ads ?
compteur
 Ranking-Hits PageRank for this page