SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Zealot
    Join Date
    Aug 2000
    Location
    Washington, DC
    Posts
    103
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Problem adding mysql fulltext index

    Hello all, I hope somebody can cast some light on this for me!

    Preliminary info:
    - Large mysql 4.0.23 database (1.3 million rows)
    - dedicated server (dual opteron, 2gigs ram)

    Problem:
    I'm re-writing the search function for a software package that wasn't really designed to handle this many rows (search out of box uses SELECT... WHERE x LIKE '%search string%', ugh). I'm going to implement search using MySQL's fulltext search.

    I enter the command to add the index (pasted below), and it seems like mysql is working on it (the mysql prompt doesn't return). It stays like this for several hours but the index is not added. A 'top' command shows that the server load is not above normal levels at all.

    I've tried 4 methods for adding the index:

    Manually via mysql prompt
    -ALTER TABLE tablename ADD FULLTEXT (col1, col2, col3);
    -CREATE FULLTEXT INDEX full_index ON tablename(col1, col2, col3);

    Used PhpMyAdmin to add fulltext index on col1, col2, col3

    Linux prompt (ran over night):
    nohup mysql -e "CREATE FULLTEXT INDEX full_index ON tablename(col1, col2, col3);"&

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    on a table with that many rows, it could hours to create that index and you're not going to see mysql using a lot of processor time in top since creating an index is mostly a disk-based operation.

    to speed this up, you could try increasing the size of key_buffer and table_cache. key_buffer should be at least as large as the projected size of your fulltext index and a larger table_cache will allow mysql to load alrger swaths of data in to memory, cutting down on the number of non-sequential reads the OS has to do.

  3. #3
    SitePoint Zealot
    Join Date
    Aug 2000
    Location
    Washington, DC
    Posts
    103
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks longneck, that analysis is consistent with what I've been reading since posting my question. I'll try those changes you recommended.


Bookmarks

Posting Permissions

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