SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Guru risoknop's Avatar
    Join Date
    Feb 2008
    Location
    end($world)
    Posts
    834
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Fulltext seach AND InnoDB

    Is there any alternative to FULLTEXT in InnoDB tables? I am using InnoDB tables because I need foreign keys and transactions but on the other side, I need also FULLTEXT.

    I was thinking of creating an additional table of MyISAM type where I would store the same content as in InnoDB table and I would use this new table just for full text searching.

    But that's sounds like a really inefficient solution. Is there anything other I could do?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    that's actually the best solution

    the extra myisam table needs to have only the primary key and the fulltext column

    do your search on this table, and join to the other tables via the key

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru risoknop's Avatar
    Join Date
    Feb 2008
    Location
    end($world)
    Posts
    834
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Is this ok if there will be no autoincrementing key? Like this?

    Code:
    CREATE TABLE pages_fulltext (
        page_id INT NOT NULL,
        title VARCHAR(255) NOT NULL,
        body TEXT NOT NULL,
        PRIMARY KEY (page_id),
        FULLTEXT (title, body)
    ) ENGINE = MYISAM;

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    i don't know where the idea comes from that all tables need an auto_increment, because they don't

    and this one certainly doesn't

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru risoknop's Avatar
    Join Date
    Feb 2008
    Location
    end($world)
    Posts
    834
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you. I wasn't sure


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
  •