Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2006
    Posts
    15

    Unanswered: How to store and index a big string?

    I need to store several millions of string with a length from 20 up to 700 characters. The sources is UTF8, so I need to create an UTF8 column to store it.
    I know MySQL has limitation in the number of bytes it can index and this is also affected by the character set, so I get errors when I try to create an index on this column.
    As I'm almost sure I'm not the first trying to do this, I guess there might be some known tricks to workaround this. One I came up with is to use a hash function and use this for the key, the problem is that no hash function will guarantee a unique key.
    Any suggestion is very welcome.

  2. #2
    Join Date
    Aug 2005
    Posts
    30
    Try FULLTEXT index:
    This can handle your large data request.

    mysql> CREATE TABLE articles (
    -> id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    -> title VARCHAR(200),
    -> body TEXT,
    -> FULLTEXT (title,body)
    -> );

    mysql> SELECT id, MATCH (title,body)
    -> AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE) AS score
    -> FROM articles;

  3. #3
    Join Date
    May 2007
    Posts
    4
    Yes, the FULL TEXT index is the best option to index text based columns and do text based searches, although you do have to take care of the following things:

    1. Full-text indexes can be used only with MyISAM tables, and can be created only for CHAR, VARCHAR, or TEXT columns.

    2. MySQL doesn't index small words (length 3 or less) by default.

    Brijneet Bhasin
    DB Team @ Clarion
    Clarion Technologies
    (SEI CMMI Level 3)
    Pune, India
    www.clariontechnologies.co.in
    www.vEmployee.com
    Last edited by brijneetclarion; 05-16-07 at 09:05.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •